SAS DATA Step Interview Questions: PDV, BY Groups, Retained Values, and Merges

SAS DATA step interview questions become much easier when you stop treating the program as a SQL query and start tracing its execution. The important questions are what enters memory, which values survive the next iteration, and exactly when an observation is written.

The direct answer: explain the program data vector, distinguish ordinary assignments from retained accumulators, reset group state on FIRST., and predict match-merge output before running anything. Those four habits expose most mistakes in small DATA step exercises.

This guide uses conventional Base SAS DATA step behavior and original practice data, not verified employer questions.

What is the program data vector?

The program data vector, or PDV, is SAS's working area for building an observation. Compilation establishes variables and their attributes; execution reads and transforms values. The output data set contains selected values from that working area, not every piece of bookkeeping SAS maintains.

Two useful automatic variables are _N_, which tracks DATA step iterations, and _ERROR_, which indicates certain processing errors. Neither is automatically written to the output. Do not describe _N_ as an output row number: an iteration may write zero, one, or several observations.

Imagine an input table with patient, visit, and dose. The DATA step also calculates a running dose. Draw those four columns as the working state, and put output observations on a separate page. That separation prevents a common interview mistake: assuming that changing a value after an explicit output retroactively changes the row already written.

SAS's DATA step processing documentation supplies the execution model; the traces here are deliberately small enough to verify by hand.

Which values reset between iterations?

An ordinary variable created by an assignment normally starts each iteration missing. A retained variable keeps its previous value. Variables read with SET or MERGE have their own automatic retention behavior, so the blanket statement that every PDV value is cleared is incorrect.

Consider flag=1 only when dose exceeds 10. Without retention, other iterations leave the assignment-created flag missing. With retain flag 0, one high dose makes the flag remain 1 until another statement resets it. Neither behavior automatically means "this patient's high-dose flag."

To answer precisely, identify the variable's origin, initialization, update condition, and reset condition. If the interviewer changes the task from an observation flag to an ever-observed patient flag, your reset policy must change with it.

A useful diagnostic question is: could the first row of patient B inherit patient A's result? If yes, a patient boundary reset is probably missing. Retention is storage behavior; it does not understand your clinical or business grouping.

How do three accumulation expressions differ?

SAS documents the sum statement as an accumulator that is automatically retained and initially zero. A missing expression contributes zero. That differs from both ordinary addition and a call to the SUM function without retention.

Expression	State across iterations	Missing dose behavior
total=total+dose;	Needs explicit retention	Missing can propagate
total=sum(total,dose);	Needs explicit retention	SUM ignores missing arguments when a nonmissing argument exists
total+dose;	Automatically retained	Missing dose contributes zero

For doses 4, missing, and 7, a sum statement initialized at zero produces 4, 4, and 11. A retained ordinary addition produces 4, missing, and missing. A nonretained SUM assignment does not create a running total merely because the function ignores missing arguments.

Ask whether a missing dose means zero administered, unknown administered, or invalid measurement. The sum statement answers a computational question; the analysis specification must answer the meaning question. Keep a separate missing count when an apparently complete total would otherwise hide incomplete evidence.

Trace a complete BY-group total

Assume visits is sorted by patient and visit, with input rows A/1/4, A/2/missing, A/3/7, and B/1/2. Here is a one-row-per-patient summary:

data totals;
  set visits;
  by patient;
  if first.patient then do;
    total=0;
    missing_count=0;
  end;
  total+dose;
  missing_count+missing(dose);
  if last.patient then output;
  keep patient total missing_count;
run;

Input	FIRST.patient	LAST.patient	Total after update	Output
A, visit 1, dose 4	1	0	4	None
A, visit 2, missing	0	0	4	None
A, visit 3, dose 7	0	1	11	A, 11, 1
B, visit 1, dose 2	1	1	2	B, 2, 0

The order matters: reset, add, then decide whether to write. Moving output before addition would omit each patient's final dose. Removing the reset would give B a total of 13. These are observable failures, not stylistic disagreements.

What do FIRST. and LAST. actually describe?

FIRST.patient and LAST.patient are temporary BY-group indicators, not columns that must already exist. A one-observation group has both indicators set. They describe boundaries in the processed sequence, not the globally earliest or latest clinical event unless ordering makes that true.

For by site patient, a change in site also establishes a new patient group boundary even if the printed patient identifier repeats. Suppose site 1 has patient 8 and site 2 also has patient 8. Those are separate composite groups, and a site change must not carry the first site's accumulator forward.

If asked for the latest visit, define the sort variables and tie rule. Two records with the same date need an additional ordering criterion or an explicit ambiguity policy. "Take LAST.patient" is incomplete until you can explain why the final record represents the required visit.

A missing identifier also needs a policy. Grouping unknown patients together may be technically consistent with the data but analytically wrong.

Why do ordering and filtering change results?

For ordinary BY processing, arrange input in the required order or use an appropriate index. NOTSORTED has a narrower purpose for supported operations: it treats contiguous equal values as groups. It does not globally collect scattered patient records, and it is not a substitute for ordered match-merge input.

Consider A, B, A as the incoming patient sequence. Treating contiguous runs as groups creates two A groups. If you expected a single A total, the logic is wrong even if the program finishes without your anticipated sort error.

Filtering deserves the same care. A WHERE condition selects input observations before normal DATA step processing; a subsetting IF executes within the step. A condition based on a newly calculated variable therefore belongs after that calculation, not in an input WHERE that cannot see it.

For group summaries, define whether excluded observations should influence totals and boundaries. Removing a visit before aggregation answers a different question from suppressing the final summary after computing all visits.

Why is a match merge not a SQL join?

For a BY group with repeated keys in both inputs, a DATA step match merge does not produce the Cartesian combinations associated with a many-to-many equijoin. SAS's MERGE reference explains that its output count follows the largest group count among inputs.

Use one key K. The left input has amounts 10 and 20; the right input has labels X, Y, and Z. With distinct non-key variable names and a straightforward merge left right; by key;, the three outputs are 10/X, 20/Y, and 20/Z. The exhausted left input's value remains available within that BY group.

A SQL equijoin on K would produce six combinations. Neither answer is inherently right for the business problem. If every purchase must pair with every applicable offer, the three-row match merge is unsuitable. If the intended relationship is one record per key, duplicate keys should trigger investigation before either operation.

Predict the expected row count before coding. It is one of the cheapest checks against a plausible-looking but incorrect result.

What do IN= flags prove?

IN= options create temporary indicators associated with the contributing input data sets. In BY-group match merging, they support selecting groups present in both inputs or retaining groups from one side. They do not prove that a fresh observation was read from each input on every output iteration.

That distinction matters in the two-versus-three example. The third output can retain the left-side amount even though the left group has no third record. Calling that row "the third matched purchase" invents a relationship that the data never contained.

When the requirement is a patient roster enriched with one demographic record, first confirm uniqueness on the demographic side. Then investigate unmatched patients separately. An if a and b filter may remove important missing-data evidence rather than resolve it.

Explain the intended grain in plain English: one row per patient, one row per visit, or one row per legitimate pair. Only then describe the merge and filters that implement it.

How do shared names and lengths cause silent damage?

Two merged inputs can contain a variable with the same name but different meanings. Even when names represent the same concept, later reads can overwrite earlier values. Automatic retention during match merging makes shared-variable behavior especially easy to misread.

Prefer explicit renaming when diagnosing a conflict: keep dose_left and dose_right, compare them, and derive a final dose according to a documented rule. If missing on the right means "no update," do not assume a generic merge applies that policy automatically.

Character length is another compile-time concern. If a character variable is established with insufficient length, later longer values can be truncated. Define the intended length before the statements that would establish a shorter attribute, and verify the descriptor information.

For an interview scenario, use status values OK and REVIEW. Ask what happens if a derived status variable is first established as length two. The failure is especially dangerous because counts can remain correct while labels lose meaning.

When does OUTPUT freeze an observation?

An explicit OUTPUT writes the current values at that point. Its presence also changes the default output behavior: do not expect an additional automatic observation at the step's end.

Take a one-iteration example with total=0; output; total=5;. The written value is zero. Moving output after the assignment writes five. Adding a second explicit output writes both states. This is why an output statement belongs in a trace as a separate event.

For a real scenario, expand one transaction into debit and credit entries. Assign the debit fields, output, assign the credit fields, output. Then verify that fields unrelated to the amount are intentionally populated for both observations. A field left over from the first entry can leak into the second.

Also inspect early exits and subsetting conditions. A record that never reaches output is absent regardless of how convincing its intermediate calculations look. Count input reads, loop iterations, and written observations separately.

How should you debug an unfamiliar step?

Start with three or four rows that challenge the contract: one singleton group, one missing measurement, a repeated key, and an unmatched record. Write expected output before running the code. Add temporary logging of identifiers, boundary flags, retained variables, _N_, and output decisions.

Read the SAS log alongside the resulting data. Uninitialized variables, conversion notes, truncation concerns, and repeated BY values are evidence to investigate. A successfully created data set is not proof that its grain or values satisfy the specification.

For practice, explain a failing total in this order: the intended patient summary, the first incorrect iteration, the retained value that crossed the boundary, and the reset that fixes it. Then test a single-row patient because that case exercises initialization and final output together.

Finish with reconciliation: input patients versus output patients, known doses versus totals, missing counts, and expected merge cardinality. Those checks connect DATA step semantics to the analysis someone will actually use.

Sources and Further Reading

• SAS 9.4 DATA Step Statements reference covers BY, OUTPUT, and statement details.
• SAS DATA step processing explains iteration and retention.
• SAS RETAIN statement describes initialization and exceptions.
• SAS sum statement specifies accumulator and missing-value behavior.
• SAS MERGE statement documents match-merge cardinality.
• SAS combining data sets explains PDV behavior when combining inputs.

Sources reviewed September 24, 2026. Several official references describe older Base SAS releases; the article does not claim CAS execution equivalence.