SAS PROC SQL Interview Questions: Remerging, Missing Values, and Unexpected Row Counts

SAS PROC SQL can return a perfectly valid result that surprises someone trained on another SQL engine. A grouped query can remerge an aggregate with detail rows; numeric missing values can satisfy a less-than condition; and a many-to-many join can multiply observations before the final aggregation. In a SAS interview, the answer is not “PROC SQL is weird.” It is to predict the exact rows, identify the grain at each stage, and rewrite the query to match the requested output. This guide uses an original five-row fixture, with behavior checked against the SAS SQL Procedure User's Guide. The fixture is practice, not an employer-specific question report.

The crucial distinction is between a query that should return one row per group and a query that should return one row per detail observation with a group statistic attached. PROC SQL can support both, but a mixed SELECT list may choose the latter through remerging.

Interview prompt: why did two groups produce five rows?

Assume a SAS data set work.scores has these observations:

person	team	amount
Ada	A	10
Ben	A	20
Cy	A	.
Dia	B	5
Eli	B	15

The dot is a SAS numeric missing value. Team A's nonmissing average is 15, Team B's is 10, and there are five detail observations. Now consider:

proc sql;
  select team, person, amount, mean(amount) as team_avg
  from work.scores
  group by team;
quit;

The query asks for person and amount, which are detail columns not grouped by team. PROC SQL can calculate each team's summary and remerge it onto the detail observations. The expected instructional result is five rows: Ada A/10/15, Ben A/20/15, Cy A/./15, Dia B/5/10, and Eli B/15/10. Do not claim that GROUP BY team alone guarantees two output rows. The SAS guide's discussion of remerging describes a second pass that associates summary values with detail data. If you run this in SAS, inspect the log for its remerge note and compare the row count.

The output order is not guaranteed unless you add an ORDER BY; the five named records and values are the point. We did not run a SAS engine in this writing environment, so this is a documented-behavior prediction and hand-checked arithmetic, not a claimed execution trace. That evidence boundary matters when you turn a practice answer into production code.

Question 1: When should PROC SQL remerge, and when should it not?

Remerging is useful when the requested grain is detail rows plus a group statistic. Suppose the task says, “For each person, show the team average next to their amount.” Five rows are then correct, and the repeated 15 or 10 is intentional. If the task says, “Return one average per team,” the same SELECT list is wrong because person and amount have no single meaningful value per team. The correct aggregate-only query is:

proc sql;
  select team, mean(amount) as team_avg,
         count(*) as row_count, count(amount) as measured_count
  from work.scores
  group by team;
quit;

This has two expected rows. Team A: average 15, row_count=3, measured_count=2. Team B: average 10, both counts 2. count(*) counts observations, while count(amount) counts nonmissing amount values. The average uses nonmissing values. Stating all three outputs prevents the common silent error of treating a missing amount as zero or treating it as another observation in the average denominator.

If you want the five-row output but would rather make the two-stage operation explicit, compute team summaries first and join them back on a unique team key. That makes the desired detail grain visible to a reviewer. A DATA step can also merge sorted detail and group summary tables when its BY-group conditions are satisfied. The choice is not an ideological SQL-versus-DATA-step rule; it depends on clarity, data size, and the requested output. In an interview, explain why the row count is five or two before choosing syntax.

SAS also provides controls such as PROC SQL's NOREMERGE option and the NOSQLREMERGE system option. The official SQLREMERGE reference says an attempted remerge under NOSQLREMERGE produces an error rather than silently returning those detail rows. That can be a guardrail in a codebase where unplanned remerging is considered a defect. It is not a substitute for defining the output grain: a deliberate detail-plus-summary query needs an explicit alternative if remerge is disabled.

Question 2: Why does a less-than filter include a missing amount?

Ask what this query returns from the fixture:

proc sql;
  select person, amount
  from work.scores
  where amount < 10;
quit;

In PROC SQL's handling of SAS numeric missing values, missing is ordered below nonmissing numbers. The expected rows are Cy (missing .) and Dia (5). A candidate importing ANSI SQL intuition may expect only Dia because a SQL NULL comparison normally evaluates to unknown. The SAS procedure guide explicitly warns that a less-than predicate can include missing values and shows excluding them with IS NOT MISSING.

If the business rule is “known measured amounts below ten,” say it and write it:

where amount < 10 and amount is not missing

Then only Dia remains. If missing amounts should be flagged as a separate data-quality category, do not discard them invisibly. Report known_below_threshold, known_at_or_above_threshold, and missing separately, or surface a missing indicator alongside the value. An interview answer should be explicit about whether missing means “not measured,” “not applicable,” or “zero”; those are different business states.

SAS supports special numeric missing values such as .A in addition to ordinary .. The exact ordering details can matter for sort and comparison behavior; do not casually map them all to an ordinary database NULL during migration. For this fixture, only ordinary . is used. The general defensive rule is simple: when a numeric comparison should apply to observed values, include a missing-value condition and test the missing case.

Question 3: Why did a join grow five rows into eight?

Now imagine a work.team_labels table with two label rows for team A and one for team B:

team	label
A	north
A	priority
B	standard

Joining the five detail rows on team produces 3 × 2 = 6 rows for A and 2 × 1 = 2 for B, for eight total. If you then sum amount, A's 10+20 is counted twice and becomes 60; B remains 20; the reported grand total becomes 80 instead of the source detail total 50. The missing amount does not add a numeric value, but Cy's row is still duplicated. This is join cardinality, not remerging. Conflating the two yields a patch that fixes the wrong layer.

First ask whether each label is a valid independent category. If the request is “one row per person with a single current label,” select one label per team using a documented effective-date or priority rule before the join. If the request is “one row per person-label,” eight rows are correct, but you cannot naively aggregate amount across those rows as though each represented a unique person. You might allocate or deduplicate according to a defined metric, not with arbitrary DISTINCT on the numeric amount. Two people may legitimately have the same amount.

A good diagnostic query counts records and keys at every stage: five detail rows, three label rows, two team keys, eight joined rows, five distinct people. Then check sums on the correct grain. If an inner join unexpectedly returns fewer than five people, inspect missing keys and values present on only one side. If it returns more, inspect key multiplicity. The general lesson is to state the expected join relationship—one-to-one, many-to-one, or many-to-many—before using the result as a metric source.

Question 4: How do you rewrite the problem without hiding it?

Start with the requested result. For one row per team, use the aggregate-only query shown above. For one row per person plus team average, either accept documented remerge deliberately or make a summary table and join it back to detail on team. For one row per person plus a single team label, reduce labels to one row per team using a business rule, then join. Each plan has an observable cardinality: two teams, five people, or eight person-label pairs. Write that number beside the query during review.

Use the SAS log as evidence. A remerge note tells you PROC SQL performed a two-pass operation; it does not by itself say the result is wrong. A warning about a Cartesian product or many-to-many join should prompt a cardinality check. Compare count(*), count(distinct person) where person is truly unique in this fixture, and source totals before and after each join. In real data, a name is seldom a safe unique key; choose a stable person or transaction identifier.

Be careful with SQL dialect transfer. An ANSI SQL tutorial may reject selecting ungrouped person beside a grouped average, while PROC SQL can remerge. A database NULL under a less-than predicate may be excluded, while SAS missing can be included. An interviewer who asks about PROC SQL is testing whether you can distinguish the runtime you are actually using. State the assumed SAS version and whether a query is executed by SAS locally or pushed down to an external DBMS; pushdown can change the semantics that apply. If you cannot verify pushdown, do not claim one engine's result with certainty.

One useful regression set is tiny: the five rows above; a team with all amounts missing; a team with no label; two labels for one team; and duplicate person IDs in source. Assert output row counts, team averages, missing-value inclusion, and sums after the join. These cases turn a verbal answer into evidence. They also show why a “works on my sample” result can fail when one new many-to-many key appears.

Sources and Further Reading

• SAS 9.4 SQL Procedure User's Guide — official remerge, SQLREMERGE, and missing-value behavior.
• SAS Viya SQL Procedure User's Guide — official example of missing values in a less-than predicate; language/version differ from the primary SAS 9.4 reference.