Blog › ICP guides

SAS developer on retainer: PROC SORT NODUPKEY semantics, SAS macro language, clinical data processing, and SAS on monthly retainer

October 3, 2026 · ~14 min read

A SAS developer was maintaining a clinical trial data processing application at a pharmaceutical contract research organization (CRO) running SAS 9.4 on Linux. The application processed ADSL (Analysis Data Subject-Level) datasets for clinical efficacy analysis, following CDISC ADaM standards. The developer ran PROC SORT DATA=adsl OUT=adsl_sorted NODUPKEY; BY subject treatment; to remove what they assumed were true duplicate records — rows where all values were identical — before the primary efficacy analysis. The dataset had 3 observations per subject (baseline, week4, week8 timepoints) and 8 subjects, totaling 24 observations. NODUPKEY kept only the first observation per unique subject treatment key combination and silently deleted the remaining 2 observations per subject — because subject treatment was the BY variable combination, and each subject had the same subject treatment value across all three timepoints. The result: 16 observations deleted (8 subjects × 2 deleted per subject), leaving only 8 baseline observations. Two efficacy analyses ran on this incomplete dataset and produced wrong statistical summaries. When the QC reviewer compared output row counts (expected 24, saw 8), the investigation began.

The root cause is the distinction between NODUPKEY and NODUPRECS. NODUPKEY keeps the first observation for each unique combination of BY variables, regardless of what any other variable in the dataset contains. NODUPRECS keeps the first observation only when all variables in the two consecutive observations are identical — a true duplicate. The developer’s intent was NODUPRECS: remove rows that are completely identical to another row. NODUPKEY instead treats the first observation per unique BY key as the only keeper, silently dropping all subsequent observations for that key, even when those subsequent observations have completely different values in non-BY variables (like the timepoint variable, or aval analysis value). SAS documents this behavior clearly (SAS 9.4 PROC SORT documentation), but the NODUPKEY/NODUPRECS distinction is one of the most common SAS data processing errors in clinical trial programming contexts, precisely because “remove duplicates before analysis” is a standard data hygiene step and NODUPKEY is the default deduplication option most developers know.

The fix was to replace the PROC SORT NODUPKEY with a proper duplicate identification step: PROC SORT DATA=adsl NODUPKEY; BY subject treatment timepoint; — but this only helps when there is a unique key that should be preserved per row. In this case, since the adsl dataset should have exactly one record per subject per timepoint per treatment (no true duplicates), the correct fix was to identify any true duplicates first with a PROC FREQ or DATA step count, confirm the dataset had no true duplicates, and remove the NODUPKEY entirely — the dataset was already clean. For cases where true duplicate removal is needed, the pattern is: PROC SORT DATA=adsl; BY _all_; followed by DATA adsl_dedup; SET adsl; BY _all_; IF FIRST.last_var; — this keeps only the first occurrence of each truly-identical row combination. Two wrong efficacy analyses: 2 → 0. The investigation to identify the 16 missing observations and trace them to NODUPKEY was 3.5 hours.

SAS data processing, PROC SORT semantics, and NODUPKEY vs NODUPRECS

SAS 9.4 is organized into licensed products. BASE SAS is the foundation: the DATA step, PROC SORT, PROC PRINT, PROC FREQ, PROC MEANS, and PROC UNIVARIATE cover data manipulation, sorting, and basic descriptive statistics. SAS/STAT adds analytical procedures: PROC GLM for general linear models, PROC MIXED for mixed models with repeated measures, PROC LOGISTIC for logistic regression, and PROC LIFETEST for time-to-event (Kaplan-Meier) analysis — all of which are used in clinical trial efficacy analyses. SAS/ACCESS provides connectivity to external databases (Oracle, SQL Server, PostgreSQL) via LIBNAME engine statements, enabling SAS programs to read and write to relational databases directly using SAS dataset syntax. SAS Enterprise Guide is the Windows-based GUI for SAS 9.4, offering a point-and-click interface for building and running SAS programs and managing SAS projects; SAS Studio is the modern web-based IDE (accessed via browser) that replaced Enterprise Guide for new deployments and supports SAS 9.4 and SAS Viya. SAS Viya is SAS Institute’s current platform, a cloud-native architecture that separates the compute tier (CAS: Cloud Analytic Services) from the data tier and supports Python, R, and Lua alongside SAS language; SAS Viya replaces SAS 9.4 for new deployments and provides REST APIs, containerized deployment on Kubernetes, and improved scalability for large-scale clinical data processing.

The DATA step is the core SAS data processing construct. It operates using the PDV — the Program Data Vector — a temporary memory buffer that holds exactly one observation at a time as SAS processes each row of the input dataset. The SET statement reads observations one at a time from a dataset into the PDV; MERGE reads from multiple datasets simultaneously into a shared PDV, matching observations by BY variable values; UPDATE applies transactions from a second dataset to a master dataset. BY statement processing in a DATA step generates two boolean flags for each BY variable: FIRST.varname is true for the first observation in each BY group, and LAST.varname is true for the last. RETAIN causes a PDV variable to preserve its value across iterations rather than being reset to missing at the start of each DATA step loop — essential for running totals, flags, and group-level aggregation. ARRAY defines an indexed set of variables for iteration: ARRAY avals{3} aval1-aval3; followed by a DO loop iterates all three analysis value variables without repeating code. OUTPUT writes the current PDV to the output dataset explicitly; without an explicit OUTPUT, SAS writes the observation at the end of each DATA step iteration automatically. DELETE skips writing the current observation to the output dataset and returns to the top of the DATA step for the next input observation. IF-THEN-ELSE controls execution flow; the IN= dataset option creates a boolean indicator variable set to 1 when an observation was read from that specific dataset (used in MERGE to detect unmatched observations); the OBS= and FIRSTOBS= dataset options control which observations are read.

PROC SORT is SAS’s procedure for sorting datasets and optionally removing duplicates. The BY statement specifies the sort key; a DESCENDING keyword before any variable name reverses the sort order for that variable. The OUT= option creates a new sorted dataset, leaving the original DATA= dataset unchanged; without OUT=, PROC SORT sorts the DATA= dataset in place. NODUPKEY removes all observations after the first for each unique combination of BY variables — regardless of what any non-BY variable contains. If the BY variables are subject treatment and the dataset has three rows per subject with identical subject/treatment values but different timepoint and aval values, NODUPKEY deletes the second and third rows and keeps only the first, producing wrong results for any analysis that requires all three timepoints. NODUPRECS removes only rows where every variable in the row is identical to the preceding row (after sort) — a true duplicate record. The EQUALS option (on by default) preserves the original relative order of rows with identical sort keys, ensuring that the first row kept by NODUPKEY is the first row in the original dataset order. SAS handles missing values in PROC SORT BY variables as less than any non-missing value: a missing numeric is treated as the smallest possible number, so missing values sort first in ascending order and last in descending order. This affects FIRST./LAST. logic in any subsequent DATA step: the first observation in a BY group may be the one with a missing BY variable value, not the one with the smallest non-missing value. SAS date and time values are stored as numeric variables (number of days since January 1, 1960 for dates; number of seconds since midnight for times) and sort numerically; applying a date format (DATE9., MMDDYY10.) affects display but not sort order. The _ALL_ keyword in a BY statement means all variables in the dataset, in the order they appear in the dataset, used in PROC SORT DATA=adsl; BY _all_; before a DATA step deduplication using FIRST./LAST. on the last variable.

CDISC (Clinical Data Interchange Standards Consortium) defines two primary standards for clinical trial data: SDTM (Study Data Tabulation Model) for collected study data and ADaM (Analysis Data Model) for analysis-ready datasets. ADSL (Subject-Level Analysis Dataset) must contain exactly one row per subject and includes baseline characteristics, treatment assignment, and subject-level flags; NODUPKEY is most dangerous when applied to datasets that are not ADSL but have ADSL-like naming — specifically BDS datasets that look like one-row-per-subject from column names alone. ADAE (Adverse Event Analysis Dataset) contains one row per adverse event per subject; BY subject NODUPKEY on ADAE would delete all adverse events after the first per subject, silently producing an incorrect safety summary. The BDS (Basic Data Structure) is the most common ADaM structure and the one most at risk: one row per subject per parameter per analysis visit, where PARAM/PARAMCD identify the analysis parameter (e.g., PARAM=“Systolic Blood Pressure”, PARAMCD=“SYSBP”), AVAL contains the numeric analysis value, AVALC contains the character analysis value, ADT is the analysis date, ADY is the analysis day relative to the first dose date, and ANL01FL through ANL99FL are analysis flags indicating which observations are included in specific analyses (ANL01FL=“Y” for the primary analysis). The BDS structure requires that subject + PARAMCD + ADT (or ADY) form a unique key per row; applying NODUPKEY with BY subject paramcd on a BDS dataset silently deletes all but the first timepoint per subject per parameter. SDTM domains include DM (Demographics), AE (Adverse Events), LB (Laboratory), VS (Vital Signs), CM (Concomitant Medications), and MH (Medical History); SDTM uses controlled terminology and must pass CDISC Pinnacle 21 validation before FDA submission. Sponsor-specific analysis conventions may define additional flag variables, additional key variables, or dataset structures that differ from CDISC defaults in ways that change which PROC SORT NODUPKEY BY variable combinations are safe and which are dangerous.

Typical SAS retainer work and what it looks like in a work log

The NODUPKEY vs NODUPRECS misuse is the most invisible category of SAS clinical data retainer work. The procedure runs successfully with no SAS errors, no SAS warnings. The SAS log prints a NOTE — “16 observations with duplicate key values were deleted” — but in production pipelines, SAS log notes are commonly filtered or not reviewed during routine runs; only ERRORs and WARNINGs are flagged by automated log-checking utilities. The output dataset looks syntactically valid: it has all the expected variables, correct variable types, and correct formats. The only symptom is the wrong observation count, which only surfaces when a QC reviewer runs an explicit row-count check. Work log entry: “ADSL_PREP: PROC SORT NODUPKEY BY subject treatment; 3 timepoints per subject; NODUPKEY kept only baseline (first) observation per subject/treatment; 16 observations deleted silently; 2 efficacy analyses on incomplete dataset produced wrong summaries; removed NODUPKEY (dataset had no true duplicates); verified with PROC FREQ count by _all_; wrong analyses: 2 → 0; 3.5h.”

Macro %IF/%THEN logic with missing %SYSFUNC or %EVAL is the second most common SAS retainer pattern. The developer writes %IF &count > 0 %THEN %DO; where &count is set by %LET count = %sysfunc(countw(&varlist));, but a prior run left &count undefined in the macro symbol table — for example, because the program was submitted in a SAS session that had already defined the macro globally, but a fresh session starts without it. The SAS macro facility evaluates %IF conditions using %EVAL for integer arithmetic and %SYSEVALF for floating-point arithmetic; when &count resolves to an empty string, %IF > 0 becomes a comparison between an empty string and the integer 0, which may produce a macro error (“The expression is not valid”) or may silently evaluate as false depending on SAS version and option settings, causing the %THEN %DO block to be skipped entirely. The fix is to add a %IF %symexist(count) %THEN guard before the comparison (the %SYMEXIST function returns 1 if the macro variable exists in any scope) and to wrap the comparison in explicit %EVAL: %IF %eval(&count) > 0 %THEN %DO;. Work log entry: “EFFICACY_MACRO: %IF &count > 0; &count undefined in fresh session; wrong macro branch taken (analysis block skipped); added %SYMEXIST guard + %EVAL; wrong macro branches: 3 → 0; 2h.”

PROC MEANS/FREQ CLASS variable encoding errors round out the most common SAS retainer work. A developer uses PROC MEANS DATA=bds; CLASS treatment; VAR aval; to produce treatment-group summary statistics, but the treatment variable is stored as a numeric variable with values 1, 2, and 3, not as a character variable with values “PLACEBO”, “LOW_DOSE”, and “HIGH_DOSE”. PROC MEANS groups by the numeric treatment value correctly and performs the statistics correctly — the analysis results are right — but the output CLASS column displays as “1”, “2”, “3” rather than the treatment names. The root cause is that treatment is a numeric code that was never assigned a user-defined format via PROC FORMAT. The fix is to create a PROC FORMAT with a VALUE statement: PROC FORMAT; VALUE trtfmt 1=“PLACEBO” 2=“LOW_DOSE” 3=“HIGH_DOSE”; RUN; and then apply the format to the dataset with a FORMAT statement: FORMAT treatment trtfmt.; in a DATA step or in the PROC MEANS itself. Work log entry: “EFFICACY_SUMMARY: PROC MEANS CLASS treatment; treatment numeric 1/2/3; no user-defined format applied; table column labels showed numeric codes instead of treatment names; created PROC FORMAT trtfmt VALUE; applied FORMAT treatment trtfmt.; wrong table labels: 1 dataset → 0; 1.5h.”

SAS retainer work in clinical trial contexts also regularly includes DATA step MERGE vs PROC SQL JOIN auditing. SAS MERGE BY is not an inner join: when an observation from one dataset has no matching BY variable value in the other dataset, MERGE includes that observation in the output, filling the missing-dataset variables with SAS missing values (period for numeric, blank for character). A one-to-many MERGE — where the master dataset has one row per subject and the transaction dataset has multiple rows per subject — produces the correct result only if the transaction dataset is sorted and the MERGE is inside a DATA step that handles the one-to-many relationship explicitly. Without that handling, the master dataset’s values are re-read and re-merged for each transaction row, which is the correct behavior but surprises developers who expect MERGE to behave like a relational JOIN. When both datasets have many-to-many relationships on the BY variable (neither is unique on the key), MERGE produces a Cartesian product that matches row 1 of dataset A to row 1 of dataset B, row 2 of A to row 2 of B, and so on — not a full Cartesian product like SQL CROSS JOIN, but a paired-row match that truncates at the shorter dataset. PROC SQL SELECT with an explicit ON condition performs a proper relational join. Retainer work in this category: “ADAE_MERGE: MERGE adae adsl BY subject; adsl one-row-per-subject, adae multiple-rows-per-subject (correct one-to-many); developer added WHERE subject = adsl.subject (PROC SQL syntax inside DATA step MERGE — invalid); fixed with IN= variable IF in_adae AND in_adsl to simulate inner join behavior; unexpected missing rows in output: 12 → 0; 2.5h.” These merge-audit sessions are structurally similar to the NODUPKEY investigation: the SAS program runs without error, the output dataset has the right variables and formats, and the wrong result is only detected by an explicit count comparison or a QC check against expected row counts.

How HourTab tracks SAS retainer hours

SAS retainer work is invisible by the same mechanism that makes NODUPKEY dangerous: the procedure runs successfully with no errors, no warnings in the SAS log (a note that “N records were deleted” appears, but notes are commonly filtered from production logs), and the output dataset looks plausible. The missing observations are only detected by an explicit row-count comparison: expected 24 observations, got 8. In a large dataset with hundreds of variables and thousands of observations, this discrepancy may not surface until a QC reviewer runs a specific count check. The invisibility is compounded by the fact that PROC SORT NODUPKEY is a valid, well-documented SAS procedure option — the program does exactly what NODUPKEY specifies. The error is not in the syntax; it is in the semantic mismatch between what the developer intended (true-duplicate removal) and what NODUPKEY does (first-per-BY-key retention). The investigation requires understanding the dataset structure, the BY variables, the distinction between NODUPKEY and NODUPRECS, and the CDISC ADaM requirements for observation uniqueness in BDS datasets. That understanding takes 3.5 hours to apply and document. Without a work log, those 3.5 hours are indistinguishable from a routine program review.

The work log entry needs to name the mechanism: which SAS program, which PROC SORT step, which BY variables, what the observation count was before NODUPKEY (24) and after (8), what the intended behavior was (true-duplicate removal) versus the actual behavior (first-per-BY-key retention), what the fix was (removed NODUPKEY after confirming no true duplicates; verified with PROC FREQ count by _all_), and what the wrong-analysis count was before and after (2 → 0). A log entry that says “fixed PROC SORT deduplication issue, 3.5h” is not auditable. A log entry that names the dataset (ADSL), the BY variables (subject treatment), the observation counts (24 → 8), the NODUPKEY mechanism, the NODUPRECS alternative, the confirmation step (PROC FREQ), and the wrong-analysis count (2 → 0) is auditable and defensible in a regulated clinical trial environment where wrong analyses have FDA submission implications. HourTab gives SAS developers at CROs and pharmaceutical companies a public retainer-hours URL they send to clients: each work log entry is visible to the client without a client login, without a status email, and without a separate project management portal. For SAS retainers, each entry names the SAS procedure, the semantic property that caused the bug, and the observation or analysis count that confirms the fix.

Comparative context for SAS retainer clients and adjacent consultants: SAS retainer work has structural overlap with other enterprise batch data processing languages where a common operation silently returns a wrong result set under a specific data condition. ABAP retainers cover the SAP 4GL where FOR ALL ENTRIES IN empty-table semantics — returning all rows from the database table when the selection table is empty, rather than no rows — is the dominant class of invisible batch processing bug; like PROC SORT NODUPKEY, the ABAP SELECT runs without error and produces a syntactically valid output, and the wrong result only appears under a specific condition (empty selection table at period-end). RPG retainers on IBM i cover indicator-based batch update programs where a wrong indicator state causes an update step to execute or skip under a specific data condition, producing wrong output records in a production batch run that completed successfully with no error codes; like the SAS NODUPKEY investigation, the RPG investigation requires tracing the indicator state across program steps rather than looking for a syntax error or runtime exception. Both are cases where the production program ran to completion, produced output, and the wrong result is only detectable by comparing actual output to expected output at the record or observation level.

FAQ: SAS developer retainers