Articles
SAS PROC TRANSPOSE Interview Questions: BY Groups, Duplicate IDs, and Lost Values
Prepare for SAS PROC TRANSPOSE interview questions with BY-group checks, formatted-ID collisions, LET overwrite examples, and missing-value reconciliation.
SAS PROC TRANSPOSE interview questions become difficult when the output looks tidy but no longer represents the input. A missing value can overwrite a valid measurement, two distinct IDs can become one column name, and the same subject can appear in multiple output groups. Explain the output cell key before reaching for a syntax fix.
Official procedure boundary: SAS's TRANSPOSE reference separates BY groups, ID-derived column names and VAR-selected measures. This guide uses classic Base SAS semantics documented in the linked official references, including archived editions. It does not claim new Viya features, an employer's question bank or a universal interview format.
The examples and reconciliation rules are original interview practice. We checked their shape and selection logic locally in Python; the SAS snippets were reviewed against documentation, not executed in a SAS runtime.
How do BY, ID and VAR define the output?
Think of an output value as a cell with an address. In our exercise, the address is subject, measure and visit. BY identifies the subject group, ID supplies the visit column, and VAR chooses the measurement being moved into that cell. A successful transpose needs an agreed value for every address.
Without an ID statement, positional column names such as COL1 and COL2 do not carry a business visit key. The procedure statement documents NAME as the option controlling the variable that records the transposed measure's name; the default is _NAME_. Keeping that variable matters when several measures are transposed into separate observations.
Suppose you transpose amount and weight for two subjects. The intended grain is then one subject-measure row, not simply one subject row. Dropping _NAME_ can make two legitimate rows look like duplicate subjects and invite an incorrect deduplication step.
Official selection rule: If VAR is omitted, the VAR reference describes transposing numeric variables not listed elsewhere. Character measures require explicit selection. In our input, a numeric sequence field exists only to order updates. Accidentally transposing it creates an extra measure row; it does not make sequence part of the cell key.
State a compact contract before coding: “One row per subject and selected measure, one named column per visit, with duplicates resolved before reshaping.” That contract gives the interviewer something precise to challenge if a new measure, repeated visit or missing key appears.
Does NOTSORTED combine every occurrence of a BY value?
Official grouping requirement: The BY reference requires sorting by the BY variables or an appropriate index unless NOTSORTED is specified. NOTSORTED permits groups in a different order. It is not an instruction to globally collect all equal keys scattered through the input.
Our original sequence A, A, B, A has three contiguous subject runs: A, B, A. A NOTSORTED-style grouping therefore leaves two separate A groups. The first and last A observations do not become one subject merely because their labels match. This is a shape problem before it is a duplicate-ID problem.
If the intended output is one group per subject, establish a global grouping order. If the intended output is one row per episode, preserve an explicit episode key instead. Sorting all A rows together would be wrong when the two runs represent separate admissions or experiments.
Also separate grouping order from duplicate precedence. Sorting only by subject makes groups contiguous, but does not define which repeated visit should win. Add an explicit revision order and a deterministic tie rule when the business policy depends on latest records. File arrival order is a fragile substitute for that rule.
In a spoken answer, explain both properties: “I need contiguous subject groups, and I need a defined order within each subject-visit pair.” Calling the input sorted answers only the first question unless you name the complete sort key.
Why can distinct IDs collide in one output column?
Official name construction: The ID reference uses formatted ID values to form output variable names. Raw-key uniqueness is therefore insufficient. Naming restrictions, substitutions and truncation can also make distinct inputs converge on one name. Check the resulting column identity under the actual naming options.
Our original name register contains raw IDs 1.1 and 1.2 displayed with a zero-decimal format. Both display as 1. Under the documented numeric-name conversion, both attempt to produce _1. Two raw values have become one output address; this is not evidence that the numeric data originally contained a duplicate.
A second register uses character keys sharing the same first 32 characters but differing afterward. Under a naming policy that truncates them to 32 characters, they collide. The general repair is a stable, unique mapping from business IDs to supported output names, accompanied by a retained mapping table. Simply removing the error suppresses the signal without restoring the lost distinction.
For multiple ID components, check the composed name as well as each component. A delimiter can remove some ambiguous concatenations, but it does not prove that the final names survive every substitution or length limit uniquely. A prefix makes a naming scheme recognizable; it is not a uniqueness constraint.
SAS's IDLABEL reference distinguishes labels from variable names. A descriptive label helps a reader interpret a column, but cannot rescue two records assigned to the same actual variable. In an interview, show the raw key, formatted key and final name side by side rather than guessing from the printed heading.
What exactly does LET do with duplicate IDs?
Official overwrite rule: The LET option retains the observation with the last occurrence of an ID within the data set or BY group. It does not aggregate values or select the last valid measurement. SAS's duplicate-ID example makes order-sensitive selection explicit.
Here is our original input. A dot denotes a missing numeric amount; a missing visit is a missing ID, which has a different consequence.
| Subject | Visit | Sequence | Amount |
|---|---|---|---|
| A | baseline | 1 | 10 |
| A | baseline | 2 | . |
| A | followup | 3 | 12 |
| B | baseline | 4 | 20 |
| B | followup | 5 | 25 |
| B | missing ID | 6 | 99 |
The following documentation-reviewed demonstration deliberately allows the duplicate. It is an example of the overwrite boundary, not the recommended validity policy:
proc sort data=observations out=ordered;
by subject visit sequence;
run;
proc transpose data=ordered out=wide_let let;
by subject;
id visit;
var amount;
run;
With the declared order, the predicted A-baseline cell is missing, while A-followup is 12. The later missing observation replaces 10. B's baseline and followup are 20 and 25; its amount 99 has no visit column because its ID is missing.
Reverse the two A-baseline observations and a last-occurrence policy yields 10 instead. Neither result establishes which record is correct. This counterexample is why “add LET so the procedure finishes” is a weak answer to a data-loss question.
Without LET, duplicate output names prevent a clean transpose under the documented behavior. Inspect the log and output completeness instead of consuming a partially useful-looking result. Warning or error severity can vary across documented editions; the substantive issue is unresolved multiple observations for one output address.
How do you implement a deliberate duplicate policy?
Ask whether repeated visits are corrections, repeated measurements or independent transactions. Latest revision, latest valid measurement, sum and rejection are different contracts. A medically meaningful measurement would not automatically be summed merely because a pivot aggregator can add numbers.
For this exercise only, the agreed policy is the latest nonmissing amount for each subject-visit pair. Sequence is unique and increasing, so it supplies deterministic precedence. Exclude missing visits to a separate exception record, then use the documented DATA step BY-group flags to select eligible measurements before transposing without LET.
data latest_valid;
set ordered(where=(not missing(visit)
and not missing(amount)));
by subject visit sequence;
if last.visit;
run;
proc transpose data=latest_valid out=wide_valid;
by subject;
id visit;
var amount;
run;
The input filter here is deliberate: WHERE selection precedes BY processing, so BY sees only eligible observations. A later filtering statement inside the DATA step would not redefine which record originally received the last.visit flag. That distinction can otherwise discard the earlier valid record without selecting a replacement.
The selected cells are A-baseline 10, A-followup 12, B-baseline 20 and B-followup 25. This policy preserves 10 because a later missing amount is not eligible. If missing represents a deliberate deletion or retraction, this policy would be wrong; the contract must say how that status is represented and applied.
If revision timestamps tie, add a stable identifier or reject the ambiguity. Do not describe sort stability as a business decision. For repeated transactions, an agreed sum might be appropriate, but it changes the meaning of the cell and must retain enough lineage to reconcile the contributing rows.
Which checks reveal missing values and lost observations?
Our reconciliation ledger starts with six observations. One has a missing visit, leaving five eligible keyed observations. Those occupy four unique subject-visit cells. The repeated A-baseline key explains the difference between five input observations and four possible cells; that difference is intentional only after a policy is chosen.
The ID reference documents excluding observations with missing ID components. Retain our B/99 exception separately rather than filling it into an arbitrary visit. A missing amount with a valid ID, such as A-baseline's later dot, is a value-level issue. Neither case means the subject necessarily had zero measured activity.
For an interview-sized fixture, reconcile more than row counts. Check expected subject coverage, selected measure names, final column names, duplicate groups, rejected IDs and known cell values. Two wide tables can have identical dimensions while disagreeing on the A-baseline measurement.
A useful reverse check melts the wide result into keyed cells and compares those with the policy-resolved input. Compare at that resolved grain, not with every raw revision. A reshape cannot reconstruct overwritten versions or distinguish absent visits from explicitly missing measurements unless you preserve additional presence or lineage information.
Keep special missing categories and character lengths in view when they carry meaning. A display that shows a dot does not establish that every missing category has the same business interpretation. Likewise, including a character variable can alter the output type; the VAR reference documents character output when character measures participate. Verify type and representation before feeding the result to a numeric analysis.