Base SAS programming becomes easier to reason about when you follow what happens to each observation: how it enters a DATA step, which values sit in the program data vector, what changes before output, and what a procedure reads afterward. This guide develops that reasoning through small datasets with checkable results. You will create and combine SAS data sets, control variable attributes, calculate within groups, reshape data, diagnose log messages, and produce reports or external files. Pay close attention to distinctions that change results: WHERE versus subsetting IF, retained totals versus ordinary assignments, stored values versus display formats, and BY groups versus CLASS groups. Each planned example has a concrete result to verify.
Access and Create Data Structures: programming and short-response practice
1. Temporary and permanent data sets
A SAS data set has a member name and, optionally, a library reference. A one-level name such as COPY means WORK.COPY, which normally lasts only for the current SAS session. A two-level name such as ARCHIVE.COPY directs the member to the library assigned to ARCHIVE. In a DATA step, the names after DATA identify output data sets; SET identifies the existing input. If you name two outputs, each observation reaching the end of the step is written to both unless an OUTPUT statement changes that behavior. Assign the permanent library before running the step, and confirm that its directory is available and writable. The SAS Base Programming Specialist scope includes creating temporary and permanent data sets. Its scope does not change the basic storage rule: WORK is temporary, while an assigned file library persists beyond the session.
Worked example: Assume WORK.SALES contains exactly two observations, with ID values 1 and 2, and `C:\sas\archive` is a writable directory. Run `libname archive 'C:\sas\archive'; data copy archive.copy; set work.sales; run;`. SET reads both source observations. Each reaches the end of the DATA step, so WORK.COPY and ARCHIVE.COPY each contain IDs 1 and 2. The ARCHIVE member is stored in the assigned directory.
Watch the distinction: A one-level output name does not inherit the input's library. Reading ARCHIVE.SALES and writing `data copy;` creates WORK.COPY, even when every source row came from ARCHIVE.
2. Libraries and metadata
A libref is a short name that connects SAS code to a data library. Assign it with LIBNAME, then use a two-level name to address a member in that library. PROC CONTENTS reports metadata, including variable names, types, lengths, labels, informats and formats. Those attributes tell you how to write valid comparisons and conversions before inspecting individual values. A character STATUS holding the text `1` is different from a numeric STATUS holding the number 1, even if printed output looks similar. The type also determines how missing values appear: blank for character data and a period for ordinary numeric missing data. For an unfamiliar member, inspect CONTENTS first, then examine sample observations if value meanings remain unclear. The SAS exam content guide identifies library assignment and metadata inspection within this domain.
Worked example: Assume the library directory contains ORDERS. Run `libname archive 'C:\sas\archive'; proc contents data=archive.orders; run;`. Suppose the output lists ID as numeric and STATUS as character with length 6. To select the stored text `1`, write `where status='1';`. Writing `where status=1;` mixes character and numeric types. Inspect actual STATUS values before assuming that `1` is even a valid category.
Watch the distinction: A displayed digit does not establish numeric storage. If CONTENTS says STATUS is character, compare it with quoted text or convert it explicitly for a justified numeric calculation.
Reference: [2]
3. Reading an existing SAS data set
SET reads an existing SAS data set one observation at a time into the DATA step's program data vector. The step then executes its statements in order and, without explicit OUTPUT statements, writes one output observation when it reaches the bottom. At the next iteration, variables read by SET receive values from the next source observation; a newly calculated variable is recalculated from that row. This model lets you predict both row count and values without running the program. Check whether a WHERE, subsetting IF, or explicit OUTPUT changes the normal one-input-to-one-output pattern. Also check whether the input is exhausted: once SET cannot supply another observation, the DATA step stops. The automatic iteration counter is `_N_`; it can help diagnose a row's processing path, but it is not automatically stored in the resulting data set.
Worked example: Suppose WORK.PRICES has two rows with PRICE values 6 and 9. Run `data work.twice; set work.prices; doubled=price*2; run;`. On the first iteration, PRICE is 6 and DOUBLED is 6 × 2 = 12. On the second, PRICE is 9 and DOUBLED is 9 × 2 = 18. Both iterations reach the bottom, producing two output observations in the same order.
Watch the distinction: SET does not calculate a whole column at once. SAS processes each observation through the statements; a later assignment can replace a value before that observation is written.
Reference: [2]
4. Importing delimited files
PROC IMPORT reads a non-SAS file and creates a SAS data set. For a delimited text file, DBMS=DLM selects delimited input, DELIMITER specifies the field separator, and GETNAMES=YES treats the first record as column names. OUT= names the destination; REPLACE permits overwriting it. SAS infers variable types from scanned rows, so GUESSINGROWS affects whether later text or long values influence those types. Inspect the imported metadata and a few observations afterward, especially when early rows differ from later ones. A wrong delimiter can collapse several intended columns into one; a short scan can infer numeric data before encountering a later text code. Import settings describe the file's structure, while the resulting variable attributes describe what SAS actually created. Those are separate checks, and both matter before calculations.
Worked example: Assume `C:\data\orders.txt` contains three lines: `id|amount`, `1|10`, and `2|12`. Run `proc import datafile='C:\data\orders.txt' out=work.orders dbms=dlm replace; delimiter='|'; getnames=yes; guessingrows=max; run;`. The header supplies ID and AMOUNT. The two data lines become two observations, and AMOUNT is inferred as numeric with values 10 and 12. Check PROC CONTENTS to confirm that inference.
Watch the distinction: A `.txt` extension does not tell SAS which character separates fields. If the file uses pipes, the default delimiter may parse it incorrectly unless DELIMITER specifies `|`.
Reference: [2]
5. Reading Excel workbooks
An Excel workbook can be read through PROC IMPORT or the XLSX LIBNAME engine. PROC IMPORT creates a SAS output data set from a selected sheet; DBMS=XLSX identifies the source type, SHEET= selects the worksheet, and REPLACE controls an existing output member. The XLSX engine instead exposes workbook sheets as library members, so SET can read a sheet through a two-level name. This is useful when several sheets must be addressed by name. In either method, the workbook's first row and cell contents influence the resulting variables, so inspect types and values after reading. A sheet name is a member reference, while an OUT= name is the new SAS data set. REPLACE belongs to the import route when an existing destination must be overwritten; reading a sheet with SET follows normal DATA step output rules.
Worked example: Assume `C:\data\book.xlsx` has a Q1 sheet with a header and two data rows. One route is `proc import datafile='C:\data\book.xlsx' out=work.q1 dbms=xlsx replace; sheet='Q1'; run;`. Another is `libname xl xlsx 'C:\data\book.xlsx'; data work.q1; set xl.Q1; run; libname xl clear;`. Either produces two observations. In the import route, REPLACE permits an existing WORK.Q1 to be recreated.
Watch the distinction: DBMS=XLSX does not make every worksheet one observation. A selected sheet supplies its data rows; its header normally becomes variable names when interpreted as such.
Reference: [2]
6. Concatenating data sets
Listing multiple data sets in SET concatenates them: SAS reads every observation from the first member, then every observation from the next. The output structure is the combined set of variable names available from the inputs. If one input lacks a variable found in another, its observations receive that variable's missing value. Numeric missing usually displays as a period; character missing is blank. This operation stacks rows and does not match them by ID. Before stacking, inspect same-named variables for compatible types and suitable lengths, because a character length established from an earlier source can affect later values. If you need to identify origin, create a source flag or use the IN= data set option. Predict row count by adding input row counts unless filtering or explicit output logic changes it.
Worked example: Assume NORTH has `(ID=1, SALES=5)` and SOUTH has `(ID=2, COST=7)`, with numeric ID, SALES and COST. Run `data work.combined; set north south; run;`. The first output row is ID 1, SALES 5 and COST missing. The second is ID 2, COST 7 and SALES missing. One row from each source gives two output rows.
Watch the distinction: SET with two data sets does not join rows sharing an ID. It appends observations in source order; use a suitable merge when values must align by key.
Reference: [2]
7. One-to-one matching
A BY merge aligns observations by a shared key after each input is sorted in the same BY order. With one observation per key in each source, SAS contributes variables from both sources for a matched key and missing values from the absent source for a key on only one side. A plain MERGE keeps the union of keys. IN= flags let you distinguish left-only, matched and right-only keys during the DATA step, including when you want to restrict output to matched cases. Verify key uniqueness before relying on a one-to-one interpretation; repeated keys change how SAS pairs observations within a group. Also avoid assigning different meanings to same-named nonkey variables across inputs, because the later data set can replace a value in the program data vector.
Worked example: Assume sorted LEFT has `(ID=1, X=4)` and `(ID=2, X=6)`; sorted RIGHT has `(ID=2, Y=8)` and `(ID=3, Y=9)`. Run `data work.matched; merge left(in=l) right(in=r); by id; run;`. Output IDs are 1, 2 and 3. ID 2 has X=6 and Y=8; ID 1 lacks Y, while ID 3 lacks X.
Watch the distinction: A BY merge does not automatically keep only shared keys. To retain matched IDs alone, use source flags and a condition such as `if l and r;`.
Reference: [2]
8. One-to-many matching
When a lookup source has one observation per key and a detail source has several, a BY merge can carry the lookup values across the detail observations. Sort both sources by the key and verify that the supposed lookup side is unique. Within a BY group, SAS retains the lookup value in the program data vector as repeated detail observations are processed. This yields one output row per detail row when the detail side supplies every key and no unmatched lookup rows are included. A plain merge also outputs lookup-only keys, so use an IN= flag when the required result is strictly detail rows. Trace the first and later observations within one key to understand the carried value. Do not treat a many-to-many BY merge as a Cartesian relational join; SAS pairs rows sequentially within a group.
Worked example: Assume CUSTOMER has one row `(ID='A', REGION='East')` and ORDERS has two sorted A rows with AMOUNT 5 and 8. Run `data work.order_region; merge customer orders(in=in_order); by id; if in_order; run;`. The first output row has A, East and 5; the second has A, East and 8. There are two rows because both order observations qualify.
Watch the distinction: The single customer observation is not consumed only by the first A order. Its REGION value carries through that BY group, provided the lookup key is unique.
Reference: [2]
Access and Create Data Structures: multiple-choice practice
9. Stored dates and times
SAS stores a date as a numeric count of days from 01JAN1960. A date before that origin is negative; a later date is positive. A time value counts seconds since midnight, and a datetime value counts seconds from midnight on 01JAN1960. Because these units differ, do not subtract or compare a date and a datetime as though they were interchangeable. Formats control the printed appearance, not the underlying number. This makes ordinary date subtraction useful for elapsed days, while elapsed seconds require time or datetime values. When a displayed date appears surprising, inspect its informat, stored numeric value and format in that order. A wrong informat can read the source text incorrectly; a different format can simply display the same valid value another way.
Worked example: Assume two stored SAS date values represent 01JAN1960 and 03JAN1960. Their numeric values are 0 and 2 days from the origin, so `later-earlier` equals `2-0`, or 2 days. Applying DATE9. prints recognizable calendar dates but leaves the subtraction at 2. Interpreting the same numeric 2 as a time would mean two seconds after midnight.
Watch the distinction: A date formatted as `03JAN1960` is not stored as those nine characters. Its numeric value is 2; a format supplies the display, while an informat reads incoming text.
Reference: [2]
10. Reading and displaying dates
An informat tells SAS how to interpret incoming text; a format tells SAS how to display the stored value. DATE9. can serve both roles, but the actions occur at different stages. Reading `03JAN1960` with the DATE9. informat creates the numeric SAS date value 2. Assigning the DATE9. format then displays that number as `03JAN1960`. If source text uses another date pattern, choose an informat that fits that pattern rather than relying on the output format to repair the read. Keep a date numeric when you need date arithmetic or functions such as YEAR. For diagnosis, inspect the raw text, the numeric result without a date format, and the formatted result. This separates a parsing error from a presentation choice and prevents a character date from silently becoming the basis of later calculations.
Worked example: Suppose a supplied character variable DATE_TEXT contains `03JAN1960`. Run `data work.dates; set work.raw; sas_date=input(date_text,date9.); format sas_date date9.; run;`. INPUT reads the nine-character text into numeric SAS_DATE=2. The FORMAT statement displays that stored 2 as `03JAN1960`. Subtracting the origin date value 0 from SAS_DATE gives 2 days.
Watch the distinction: Applying DATE9. as a format does not convert character text into a SAS date. Use an appropriate informat when reading or the INPUT function when converting an existing character variable.
Reference: [2]
11. Filtering before the DATA step
WHERE selects source observations as they are read, before ordinary DATA step statements process them. This can reduce work because excluded source rows do not enter the step's calculation path. WHERE expressions refer to variables already present in the input data set, including variables addressed through a source data set option. A variable first created later in the step is unavailable to that selection. To choose between WHERE and a subsetting IF, ask when the required value exists: use WHERE for an existing source field and IF after computing a new field. Be mindful that source formats do not change the stored values being compared. The SCSBP practice questions can reinforce this execution-order distinction, but the reliable diagnostic is tracing which observations reach each statement.
Worked example: Assume WORK.RAW contains AMOUNT values 4, 11 and 15. Run `data work.large; set work.raw; where amount>10; doubled=amount*2; run;`. WHERE admits 11 and 15, so the DATA step processes two observations. DOUBLED becomes 22 and 30, respectively. The row with AMOUNT=4 never reaches the assignment, and WORK.LARGE contains two rows.
Watch the distinction: WHERE cannot select on a variable created by a later assignment in the same DATA step. If selection depends on that result, calculate it first and use a subsetting IF.
Reference: [2]
12. Filtering after calculation
A subsetting IF tests the current observation at the point where the statement appears. If its condition is false, SAS stops processing that observation and does not perform the normal output for it; the next source observation is then read. Place the IF after any assignment needed to calculate its condition. This makes it suitable for filtering on a newly created TOTAL, MARGIN or flag. In contrast, WHERE acts while reading existing source variables. Statement order matters: assignments before a subsetting IF run even for rows later rejected, while assignments after it run only for rows retained. If an expression can be missing, decide deliberately how that should affect selection. Trace each row through calculation, condition and output to check both the result values and the final number of observations.
Worked example: Assume WORK.ITEMS has `(PRICE=8, QTY=2)` and `(PRICE=3, QTY=2)`. Run `data work.selected; set work.items; total=price*qty; if total>10; run;`. The first TOTAL is 8 × 2 = 16, so that row is written. The second TOTAL is 3 × 2 = 6, so it is rejected. WORK.SELECTED contains one row with TOTAL=16.
Watch the distinction: Putting `if total>10;` before the assignment does not test the intended newly calculated total. The condition must follow the calculation it depends on.
Reference: [2]
13. Choosing output columns
KEEP and DROP statements specify which variables are written to the output data set. They do not prevent an input variable from being read or used during the DATA step. This makes DROP useful when a source field is needed to calculate a result but should be absent from the delivered data. KEEP states the columns to retain; DROP states those to remove. Choose the shorter, clearer declaration while checking that required identifiers remain. These statements affect every output data set named by the step, whereas a data set option can target one particular input or output member. The distinction matters when building several outputs at once. After the step, inspect the output structure as well as values: a correct MARGIN calculation with an accidentally omitted ID may still fail the intended downstream task.
Worked example: Assume WORK.FINANCE has one row with REVENUE=20, COST=7 and ID=1. Run `data work.result; set work.finance; margin=revenue-cost; drop cost; run;`. MARGIN is 20 − 7 = 13. The output row retains ID=1, REVENUE=20 and MARGIN=13, while COST is absent as a column. COST was still available when the subtraction executed.
Watch the distinction: DROP does not erase a variable before earlier calculations in the step. An output DROP statement removes it from the written data set, while an input DROP= option prevents it from being read.
Reference: [2]
14. Input and output data set options
A KEEP= or DROP= data set option acts on the specific data set where it is attached. On an input member, KEEP= limits the variables available to the DATA step. On an output member, KEEP= controls the variables written after calculations. This placement lets you reduce input width and deliver a narrower result in the same step. List every variable needed for calculation on the input side; excluding PRICE there would leave TAX without its intended operand. On the output side, include the identifier and final result but omit intermediate fields if the consumer does not need them. A KEEP statement without a data set attachment has a different reach when multiple outputs are present, so read the code from left to right: what enters the step, what gets calculated, and what each output receives.
Worked example: Assume WORK.PRICES has `(ID=1, PRICE=50, NOTE='trial')`. Run `data work.tax(keep=id tax); set work.prices(keep=id price); tax=price*0.1; run;`. The input option admits ID and PRICE but excludes NOTE. TAX is 50 × 0.1 = 5. The output option writes ID=1 and TAX=5; PRICE and NOTE are absent from WORK.TAX.
Watch the distinction: Placing `keep=id tax` on the input would exclude PRICE, which the calculation needs, and TAX does not exist there yet. Put each option on the data set it controls.
Reference: [2]
Manage Data: programming and short-response practice
15. Sorting into a new data set
PROC SORT orders observations according to BY variables. With OUT=, it writes a separate sorted member and leaves the input's observation order available for other work. Without OUT=, the procedure normally replaces the input data set with its sorted version. Specify ascending order by default or DESCENDING for a chosen BY variable. A BY list creates a hierarchy: the first variable is the primary key, and later variables settle order within equal earlier values. Check both the sorted output and the source when preservation matters. Sorting is often required before DATA step BY processing and BY merges; it is a preparation step, not merely a presentation choice. If duplicate keys exist, the requested ordering may not determine every tie, so add a meaningful secondary key when a reproducible within-key order is needed.
Worked example: Assume WORK.RAW has three observations whose ID values appear in order 3, 1, 2. Run `proc sort data=work.raw out=work.ordered; by id; run;`. WORK.ORDERED contains IDs 1, 2, 3 in ascending order. WORK.RAW still presents 3, 1, 2 because OUT= directed the sorted result to a separate member. Both data sets contain three observations.
Watch the distinction: BY does not require writing the result back into the source. When the original order must remain available, specify OUT= and use the new member for subsequent BY processing.
Reference: [2]
16. Removing duplicate sort keys
Duplicate sort keys and duplicate observations answer different questions. NODUPKEY removes observations with repeated BY values from the sorted result, even if their other columns differ. NODUPRECS concerns observations identical across their stored variables and should not be substituted when uniqueness is defined by a key. Before using NODUPKEY, decide which row should survive a repeated key. If its nonkey values matter, sort by a deliberate priority or resolve duplicates explicitly; otherwise the retained row may not express the business rule you intended. EQUALS preserves input order among equal BY keys for the sort, which makes first-row retention easier to reason about in a simple example. Inspect removed-row counts and the retained values, because a two-row output alone cannot show whether the correct A observation survived.
Worked example: Assume INPUT contains `(ID='A', VALUE=5)`, `(ID='A', VALUE=7)` and `(ID='B', VALUE=4)` in that order. Run `proc sort data=input out=unique nodupkey equals; by id; run;`. NODUPKEY keeps one row for A and one for B. With EQUALS preserving the A rows' input order, A with VALUE=5 is retained. The A rows were different observations despite sharing the key.
Watch the distinction: The two A rows are not identical merely because their IDs match. NODUPKEY with BY ID drops one; a duplicate-record rule would treat VALUE=5 and VALUE=7 as distinct.
Reference: [2]
17. Conditional assignments
IF-THEN/ELSE expresses a decision sequence for each observation. SAS evaluates the IF condition first; when it is true, it executes the THEN action and skips the ELSE action. When false, it executes ELSE. This makes the branches mutually exclusive and avoids a later independent IF silently replacing an earlier assignment. Define character length before assignment when output labels may have different widths, and decide how a missing score should be classified. In SAS, a numeric missing value compares below ordinary numbers, so a simple `score>=70` test falls into ELSE. If missing means unknown rather than Low, add a separate branch. Translating a documented decision rule into explicit branches is also a useful business analysis skill: test boundaries and exceptional inputs, not just typical values.
Worked example: Assume two rows have SCORE values 68 and 74, with neither missing. Run `data work.bands; length band $4; set work.scores; if score>=70 then band='High'; else band='Low'; run;`. For 68, the condition is false, so BAND is Low. For 74, it is true, so BAND is High. The threshold is a hypothetical rule for these rows, not an exam standard.
Watch the distinction: Two independent IF statements can overwrite a prior assignment if their conditions overlap. Use ELSE for exclusive outcomes, and give missing values their own branch when Low would misstate their meaning.
Reference: [2]
18. Grouping conditional statements
A DO block groups several statements under one IF or ELSE condition. SAS executes the statements between DO and END in order only when that branch is selected. This prevents a fee assignment from being conditional while a related balance assignment accidentally runs for every row. Initialize or assign every required output variable across all branches; otherwise values may be missing or carried from an earlier point in the step, depending on how the variable was created. Calculate dependent values after their inputs have been set. The condition may be a character status or a numeric test, but its data type must match the comparison. For a two-branch invoice rule, trace both branches through FEE and BALANCE, then check the resulting row count. DO and END change control flow, not the number of observations by themselves.
Worked example: Assume two invoices each have AMOUNT=100; one has STATUS='Overdue' and the other STATUS='Current'. Run `data work.bills; set work.invoices; if status='Overdue' then do; fee=10; balance=amount+fee; end; else do; fee=0; balance=amount+fee; end; run;`. The overdue row has FEE=10 and BALANCE=110. The current row has FEE=0 and BALANCE=100. Both input rows produce output rows.
Watch the distinction: Without DO and END, only the next statement belongs to an IF branch. A following BALANCE assignment may run unconditionally and use an unintended FEE value.
Reference: [2]
19. Assignments and missing arithmetic
An assignment evaluates its right-hand expression and stores the result in the variable on the left. It can create a new variable or replace an existing value for the current observation. Ordinary arithmetic with a missing numeric operand generally produces a missing result, so `price-cost` cannot determine profit when PRICE is missing. That behavior can be useful evidence of incomplete input. Replacing a missing PRICE with zero before subtraction changes the calculation and the meaning of the data: zero is now an explicit assumed price. Make that decision from the task's rule, not merely to remove periods from output. To diagnose unexpected results, trace source values, any replacement assignment, and the final expression in execution order. A character blank and a numeric period are different kinds of missing value, even though both signal absent data.
Worked example: Assume one row has COST=8 and numeric PRICE missing. With `profit=price-cost;`, ordinary subtraction yields missing PROFIT because PRICE is unknown. If the hypothetical rule explicitly treats missing PRICE as zero, run `data work.result; set work.items; if missing(price) then price=0; profit=price-cost; run;`. The revised calculation is 0 − 8 = −8, and output PRICE is 0.
Watch the distinction: A missing PROFIT is not automatically a programming error. It may correctly reflect missing PRICE. Replacing PRICE with zero requires a defensible rule because it changes both stored data and interpretation.
Reference: [2]
20. Variable names and attributes
Variable attributes affect how a SAS data set is stored and presented. RENAME= changes a variable's name at the point where the option is attached; on an input, later DATA step statements use the new name. LENGTH sets storage length, and for character variables it must be established before an assignment that could set a shorter length. LABEL supplies a descriptive heading; FORMAT controls displayed values. Neither a label nor a format changes the underlying numeric amount. Inspect both metadata and a sample value when checking an attribute change, since a formatted currency display can hide the stored number used in later arithmetic. Distinguish naming from meaning: renaming AMT to REVENUE does not prove that the amount is revenue. Likewise, a CATEGORY length of 12 reserves space for up to 12 characters but does not validate category values.
Worked example: Assume SOURCE has one row with numeric AMT=1250. Run `data work.sales; length category $12; set source(rename=(amt=revenue)); category='Retail'; label revenue='Sales revenue'; format revenue dollar10.2; run;`. The output has REVENUE stored as numeric 1250 and CATEGORY stored as `Retail` within a 12-character field. The heading can read Sales revenue, and the formatted amount displays as `$1,250.00`; calculations still use 1250.
Watch the distinction: A dollar format does not multiply or alter REVENUE. It changes its display. If the stored amount should change, write an assignment; LABEL and FORMAT alone cannot perform that calculation.
Reference: [2]
21. Finding BY-group boundaries
A BY statement makes SAS identify changes in a sorted key while it reads observations. Before using `by group;`, sort by GROUP or use data whose order is already established for that key. SAS creates temporary indicators `first.group` and `last.group`. FIRST.GROUP is 1 on the first observation of each group; LAST.GROUP is 1 on its final observation. A group with one row has both indicators set to 1. These indicators exist during DATA step processing and are not written to the output unless you assign their values to ordinary variables. With several BY variables, a change in an earlier key also starts a new group for later keys. The SAS exam content guide includes this form of subgroup processing in Manage Data.
Worked example: Suppose HAVE has GROUP values A, A, B in that order. Run `proc sort data=have out=ordered; by group; run; data flags; set ordered; by group; start=first.group; finish=last.group; run;`. FLAGS has three rows: A gives `(start,finish)=(1,0)` then `(0,1)`; B gives `(1,1)`. The lone B row starts and ends its group.
Watch the distinction: A common error is to assume FIRST.GROUP marks every A row. It marks only the first A row in BY order; the second A row has FIRST.GROUP=0.
Reference: [2]
22. Retained group subtotals
A group subtotal needs a value that survives between DATA step iterations. The sum statement, such as `subtotal + sales;`, automatically retains its target and treats a missing SALES value as zero for accumulation. At `first.group`, reset SUBTOTAL to zero before adding the current row. At `last.group`, issue `output;` so the result contains one observation per group. Without explicit OUTPUT, a DATA step normally writes every processed observation. The order of reset, addition and output matters: resetting after addition would erase the first sale, while outputting before addition would omit the final sale. Sort by the grouping variable first. If a group has one observation, both boundary indicators are true, so the same sequence resets, adds and outputs once. Keep the input sales value if the summary needs an audit trail.
Worked example: Given sorted HAVE rows `(A,4)`, `(A,6)` and `(B,3)`, run `data totals; set have; by group; if first.group then subtotal=0; subtotal+sales; if last.group then output; keep group subtotal; run;`. For A, SUBTOTAL moves from 0 to 4 to 10 and outputs 10. For B, it resets to 0, adds 3 and outputs 3.
Watch the distinction: A retained subtotal without a FIRST.GROUP reset carries A's 10 into B. B would then incorrectly show 13 instead of its own subtotal of 3.
Reference: [2]
23. Sum statement versus assignment
The DATA step sum statement has the form `total + amount;`. SAS initializes TOTAL to zero before the first iteration, retains it across iterations and adds nonmissing AMOUNT values. A missing AMOUNT leaves the accumulator unchanged. An ordinary assignment, `total=total+amount;`, follows different rules: an uninitialized TOTAL is missing, ordinary addition with a missing operand returns missing, and a variable assigned in a DATA step is normally reset to missing on the next iteration unless retained. Even `retain total 0;` cannot make ordinary addition ignore missing AMOUNT. To reproduce sum statement behavior with an assignment, retain and initialize TOTAL, then use `total=sum(total,amount);`. The SCSBP practice questions offer another setting in which to trace these iteration rules, but calculate the sequence yourself before checking an answer.
Worked example: For AMOUNT values 4, missing and 3, `data want; set have; total+amount; run;` writes TOTAL values 4, 4 and 7: zero plus 4, then no addition, then 4 plus 3. Replacing the sum statement with `total=total+amount;` leaves TOTAL missing from the first row onward because the first addition starts with missing TOTAL.
Watch the distinction: Do not treat the plus sign as sufficient to identify a sum statement. `total + amount;` and `total=total+amount;` have different retention, initialization and missing-value behavior.
Reference: [2]
24. Cleaning character values
Choose a character function by the operation you need. `SCAN(text,n,delimiter)` extracts the nth word; `SUBSTR(text,start,length)` extracts characters by position. `TRIM` removes trailing blanks, which matters when joining fixed-length SAS character values. `COMPRESS(text,chars)` removes every occurrence of the listed characters; with no second argument, its default removes blanks. `UPCASE` and `LOWCASE` change letter case without changing digits. Function order matters: remove unwanted characters before comparing a cleaned identifier, and apply case conversion when matching should ignore case. Character variables have fixed storage lengths, so a cleaned value may display without trailing blanks while the variable still has a declared length. Inspect the result and its length when a key unexpectedly fails to match. Avoid using SUBSTR for a delimiter-based field whose position varies between rows.
Worked example: Start with character RAW containing ` ab-12 `. The expression `clean=upcase(compress(raw,' -'));` removes both spaces and the hyphen, giving `ab12`, then converts letters to uppercase, giving `AB12`. By comparison, `scan(raw,2,'-')` targets the portion after the hyphen and does not produce the same cleaned identifier.
Watch the distinction: Using `compress(raw)` alone removes blanks but leaves the hyphen. If both must disappear, supply both characters in its second argument and verify the final value.
Reference: [2]
25. Numeric functions across variables
The `OF` operator passes a variable list to a function, such as `sum(of score1-score3)`. `SUM` adds nonmissing arguments and `MEAN` divides their sum by the number of nonmissing arguments. If every argument is missing, either function returns missing; a sum of zero requires an actual nonmissing zero or an explicit business rule. `ROUND(value,unit)` rounds to a specified increment, so `round(6.24,0.1)` gives 6.2. Do not round intermediate values merely because the final report shows one decimal place: early rounding can change a later total or mean. When a numeric summary represents a business measure, decide whether missing means unavailable, inapplicable or truly zero before choosing a calculation. The certified corporate FP&A professional guide provides related practice with interpreting financial measures and assumptions.
Worked example: Let SCORE1=4, SCORE2 be numeric missing and SCORE3=8. `sum(of score1-score3)` is 4+8=12. `mean(of score1-score3)` is 12 divided by two observed scores, or 6, not 4. `round(mean(of score1-score3),0.1)` remains 6.0; the displayed decimal does not add a third observation.
Watch the distinction: Dividing SUM by the number of named variables gives the wrong mean when values are missing. MEAN uses the nonmissing count, which is two here.
Reference: [2]
26. Constructing and extracting dates
SAS dates are numbers counting days from 01JAN1960. `MDY(month,day,year)` constructs such a value from calendar components, while `TODAY()` and `DATE()` return the current SAS date at execution time. A format such as DATE9. controls how the number appears; it does not change the stored value. `YEAR`, `QTR`, `MONTH` and `DAY` extract calendar components from a SAS date, not from an arbitrary integer that happens to look like a date. Check whether source data contain a date, datetime or character date before applying these functions. A datetime counts seconds from the epoch and needs appropriate conversion before date extraction. When a problem supplies a fixed calendar date, use MDY or a date literal rather than TODAY(), whose result changes when the program runs. Quarter values range from 1 through 4.
Worked example: Set `d=mdy(7,15,2024); format d date9.; y=year(d); q=qtr(d); m=month(d); daynum=day(d);`. D displays as `15JUL2024`. The extracted results are Y=2024, Q=3 because July is in the third quarter, M=7 and DAYNUM=15. The stored D remains a day count despite its formatted display.
Watch the distinction: Applying YEAR directly to character text such as `15JUL2024` confuses text with a SAS date value. Read or convert the text first, then extract calendar components.
Reference: [2]
27. Converting text to numbers
`INPUT(character-expression,informat.)` converts text into a value according to the named informat. For a simple digit string, `input(code,4.)` returns a numeric value; an appropriate date informat instead returns a numeric SAS date. The informat must match the incoming text, including punctuation where relevant. Leading zeros in an identifier are usually meaningful, so consider whether conversion to a number is appropriate: numeric 42 cannot preserve the original text `0042` by itself. Store the source character field when you may need that representation later. An invalid numeric string normally produces a missing numeric result and a log diagnostic; examine such rows before calculations. Explicit INPUT makes the intended type and interpretation visible, avoiding reliance on automatic conversion inside arithmetic or comparisons.
Worked example: Suppose CODE is character `0042`. `number=input(code,4.); result=number+8;` yields numeric NUMBER=42 and RESULT=50. The four-character source remains `0042` if retained. If the output key must still contain four digits, format NUMBER back to text deliberately; the numeric result alone records 42, not its original padding.
Watch the distinction: `put(code,4.)` is the wrong conversion direction for character CODE. INPUT reads character text into a numeric value; PUT formats a value into character text.
Reference: [2]
28. Converting numbers to text
`PUT(numeric-expression,format.)` produces character text using a format. `put(id,z3.)` writes a numeric ID in a width of three characters with leading zeros. Formats have widths, and many numeric formats pad with spaces; those spaces can become part of a constructed key if you concatenate carelessly. `CATS` strips leading and trailing blanks from each argument before joining, while `CATX` also inserts a separator. Select the width from the intended key specification, then test values near that width; a value that cannot fit may display with asterisks or another unexpected representation. PUT changes the type of its result to character but leaves the original numeric ID unchanged. Use INPUT for the reverse direction. Store the output key in a character variable long enough to hold the prefix and formatted number.
Worked example: Let numeric ID=7 and prefix be `A`. `suffix=put(id,z3.); key=cats('A',suffix);` gives character SUFFIX=`007` and KEY=`A007`. The key has four characters: one letter plus three digits. Numeric ID remains 7, so arithmetic such as ID+1 would give 8 rather than a text operation.
Watch the distinction: Assigning `key='A'||id` relies on automatic numeric-to-character conversion and can insert spaces. Use PUT with the required width, then concatenate the resulting text explicitly.
Reference: [2]
29. Iterative DO loops
An indexed DO loop repeats its body for a sequence of index values. `do year=1 to 3;` executes with YEAR equal to 1, 2 and 3 under the default increment of 1. Inside a DATA step with no input SET statement, an explicit `output;` in the loop writes a row on each iteration. Once an explicit OUTPUT statement is used, the normal implicit output at the bottom of the step is suppressed, preventing an extra final row. Place calculations before OUTPUT so each row receives the value for its own index. After the loop ends, the index has advanced beyond its final in-loop value, but that post-loop value is not automatically written in this pattern. For a loop over input observations, distinguish loop iterations from DATA step iterations when predicting row counts.
Worked example: Run `data balances; do year=1 to 3; balance=100+10*year; output; end; run;`. The three loop values produce `(YEAR,BALANCE)` rows `(1,110)`, `(2,120)` and `(3,130)`. There are three output rows because OUTPUT executes three times. The balance rule adds 10, 20 and 30 to the starting 100.
Watch the distinction: Writing OUTPUT after END would create only one row for the whole loop. To preserve each year's calculated balance, place OUTPUT inside the loop after the assignment.
Reference: [2]
30. Turning rows into columns
PROC TRANSPOSE turns values from observations into variables. Use `VAR` to choose the measure, `ID` to supply output column names from an input variable, and `BY` to make one transposed result per group. Sort the input by the BY key first. Within each BY group, each distinct ID value should identify a destination column; repeated ID values for the same measure and group need investigation because they do not describe a unique cell. The `OUT=` option names the result. Without ID, SAS assigns generated column names according to observation position, which is less useful when positions represent months or categories. Consider missing combinations: if an account has JAN but no FEB row, its FEB output value is missing. A character ID such as JAN is suitable for naming a SAS variable.
Worked example: Suppose sorted LONG has `(account,month,sales)` rows `(A,JAN,4)` and `(A,FEB,6)`. Run `proc transpose data=long out=wide; by account; id month; var sales; run;`. WIDE contains one account A row with JAN=4 and FEB=6. The two sales values have moved into named columns; there is no arithmetic aggregation.
Watch the distinction: PROC TRANSPOSE does not total JAN and FEB. It rearranges the two values into columns. If you need 10 as a total, calculate that separately.
Reference: [2]
Manage Data: multiple-choice practice
31. Conditional and nested loops
An indexed DO loop runs through its index values; nested loops run the inner loop once for each outer-loop value. To count executions, multiply the number of values in each loop when neither loop exits early. A conditional loop such as `do while(condition);` instead checks a condition before each pass and may execute zero times; `do until(condition);` checks at the end and executes at least once. For conditional loops, trace how a body statement changes the controlling value, or the loop may never end. In a DATA step, loop iterations and output observations are separate quantities: multiple iterations can yield one row if OUTPUT occurs only once, while OUTPUT inside the inner loop can yield one row per index pair. Check the placement of OUTPUT before inferring dataset size.
Worked example: With `do i=1 to 2; do j=1 to 3; output; end; end;`, the six emitted index pairs are `(1,1)`, `(1,2)`, `(1,3)`, `(2,1)`, `(2,2)` and `(2,3)`. Two outer values times three inner values equals six. Moving OUTPUT below the inner END would instead emit two rows, one per outer pass.
Watch the distinction: Adding the loop bounds gives five, but that is not the execution count. The inner loop repeats three times for each of the two outer values.
Reference: [2]
32. Intervals between dates
`INTCK` counts how many boundaries of a named interval lie between two SAS date values under its default discrete method. It does not measure elapsed time in units of fixed length. Crossing from January into February counts one MONTH boundary even when the dates are adjacent. `INTNX` shifts from a starting date by a specified number of intervals and returns a date aligned within the destination interval. Its alignment argument matters: a beginning alignment can return the first day of that month, while another alignment can target a different point. Choose INTCK when the question asks how many calendar boundaries were crossed; choose INTNX when it asks for a date in a later or earlier interval. Confirm that both inputs are SAS date values, since datetime intervals require datetime values and interval names suited to them.
Worked example: Let START=`31JAN2024` and END=`01FEB2024`, both stored as SAS dates. `intck('month',start,end)` returns 1: the February boundary lies between them, although only one day passes. `intnx('month',start,1,'b')` returns `01FEB2024`, the beginning of the next month. These functions answer different questions: boundary count versus destination date.
Watch the distinction: Interpreting INTCK's result as a full month of elapsed time is wrong here. One boundary was crossed; the actual date gap is one day.
Reference: [2]
33. Variable list forms
SAS variable lists can be compact, but their selection rules differ. A numbered range such as `Q1-Q3` selects variables with that common stem and consecutive numeric suffixes. A prefix list such as `Q:` selects every variable whose name starts with Q. A positional range using two hyphens, such as `Q1--Q3`, selects variables occupying positions from Q1 through Q3 in dataset order, including any variables between them. `OF` passes such a list as separate arguments to functions like SUM and MEAN. Before using a compact list, inspect variable names and their order with PROC CONTENTS, especially after changing a source dataset. Prefix and positional forms can silently expand when new columns appear. An explicit list is safer when only a few specific variables are intended and their naming pattern is irregular.
Worked example: Suppose Q1=2, Q2=3, Q3=4 and QUALITY=100. `sum(of Q1-Q3)` equals 2+3+4=9. `sum(of Q:)` also includes QUALITY and therefore equals 109. If QUALITY lies between Q1 and Q3 in dataset order, `sum(of Q1--Q3)` likewise includes it and equals 109.
Watch the distinction: Treating one hyphen and two hyphens as interchangeable can add unintended columns. The single-hyphen numbered range follows suffixes; the double-hyphen range follows dataset position.
Reference: [2]
34. Implicit type conversion
SAS can convert a character operand to numeric automatically when an arithmetic expression requires a number. This may make a clean digit string appear to work, but it hides assumptions about its contents and can produce log notes or invalid-data diagnostics. If a string contains letters, its numeric conversion fails and the arithmetic result is missing. Use `INPUT(code,informat.)` when text is supposed to represent a number, and decide how invalid strings should be handled before using them in calculations. If the destination should remain text, use PUT to format a numeric source. Inspect the variable type with PROC CONTENTS when a comparison behaves strangely; a printed value such as `12` does not reveal whether its underlying type is character or numeric. Explicit conversion also records the intended informat in the code.
Worked example: If character CODE=`12`, `result=code+3;` can convert CODE to 12 and produce RESULT=15. With CODE=`X`, numeric conversion fails, so RESULT is missing and the log warrants inspection. `number=input(code,2.); result=number+3;` makes the same interpretation explicit; validate missing NUMBER before trusting the result.
Watch the distinction: A successful calculation on one row does not prove CODE is numeric. Automatic conversion may have handled `12` while another row containing `X` fails.
Reference: [2]
35. Stored values and formats
A SAS format controls presentation, not the stored value used in arithmetic or comparisons. PERCENT8.1 multiplies a proportion by 100 for display and appends a percent sign; it does not replace the numeric value with the displayed percentage. This matters when filtering: a stored proportion of 0.256 is greater than 0.25, even though the report shows 25.6%. Applying multiplication in a calculation produces a different numeric variable. The displayed result can also hide precision through rounding, so two values that print identically may compare differently. Inspect an appropriate higher-precision format when that distinction matters. A format can be assigned with a FORMAT statement or data set attribute and reused by procedures. Always identify whether a question asks for the underlying value, its formatted display or a newly calculated percentage.
Worked example: Let RATE=0.256 and apply `format rate percent8.1;`. A report displays RATE as `25.6%`. `if rate>0.25;` keeps the row because 0.256 exceeds 0.25 by 0.006. Setting `percentage=rate*100;` creates numeric PERCENTAGE=25.6; it does not change RATE, which remains 0.256.
Watch the distinction: Comparing RATE to 25.6 because it displays as 25.6% misreads the format. For the stored proportion, compare it with 0.256 or another proportion.
Reference: [2]
36. Simple macro variables
A simple macro variable stores text for substitution before SAS executes the resulting statements. `%let region=west;` assigns the text `west` to REGION. In `®ion._sales`, the dot ends the macro variable name so SAS resolves REGION, then appends `_sales`, producing `west_sales`; that delimiter dot is consumed. This is useful for a dataset name or repeated text that may change in one place. If an actual period is needed after the resolved value, account for the delimiter separately. Macro variable substitution does not examine observations row by row and does not make the replacement text a character DATA step variable. Read the resolved statement as ordinary SAS code and check that it forms a valid name or expression. This lesson concerns straightforward substitution, without requiring broader macro-programming techniques.
Worked example: Run `%let region=west; data ®ion._sales; set sales; run;`. SAS resolves the output name to `west_sales`, so the DATA step copies SALES into WORK.WEST_SALES when no permanent library is specified. The period after REGION marks where its name ends; it is not present in `west_sales`. The number of copied rows equals SALES's row count.
Watch the distinction: Expecting a dataset named `west._sales` treats the delimiter as literal output. One dot terminates the macro variable reference and disappears during substitution.
Reference: [2]
37. Transpose output names
PROC TRANSPOSE can make output column names from values of an ID variable. `PREFIX=` adds text before the generated name, which is useful when an ID is numeric or too terse to communicate the measure. `NAME=` changes the name of the output column that records which input variable supplied the transposed values; it does not rename every transposed measure. With one VAR variable, that source-name column holds its name, such as SALES. With several VAR variables, the source-name column helps distinguish the output rows corresponding to each original measure. Predict the result by identifying the BY group, the VAR measure, the ID values and the naming options separately. Duplicate ID values within the same destination group can create naming conflicts, so inspect the input keys before treating the output as a unique pivot.
Worked example: Suppose LONG has ID values 1 and 2 with SALES values 4 and 6. `proc transpose data=long out=wide prefix=yr name=source; id id; var sales; run;` produces one row with SOURCE=`SALES`, YR1=4 and YR2=6. PREFIX supplies `YR`; numeric IDs supply `1` and `2`; NAME renames the source-name column to SOURCE.
Watch the distinction: `name=source` does not create columns SOURCE1 and SOURCE2. It names the column containing the original VAR variable name, while PREFIX affects the transposed value columns.
Reference: [2]
38. Choosing numeric summaries
These numeric functions answer different questions. `INT(x)` truncates the fractional part toward zero, so negative inputs require care. `ROUND(x,unit)` returns a nearby multiple of the specified unit. `SMALLEST(k,values...)` and `LARGEST(k,values...)` select an order statistic: k=1 gives the minimum or maximum, while k=2 gives the second value in sorted order. They can consider multiple arguments without first sorting observations. `RAND(distribution,parameters)` generates a pseudorandom draw from a named distribution; its output is not a summary of supplied values. Select a function from the desired operation, then test a negative and a missing value when those cases may occur. Rounding and truncation can look alike for some positive values but differ for negatives and values near a rounding midpoint. Specify the unit when reporting a rounded result.
Worked example: For X=-2.7, `int(x)` gives -2 because truncation moves toward zero; `round(x,1)` gives -3, the nearest whole number. For values 4, 9 and 2, ascending order is 2, 4, 9, so `smallest(2,4,9,2)` gives 4. `largest(1,4,9,2)` gives 9.
Watch the distinction: INT is not a floor function for negative values. Flooring -2.7 would give -3, while SAS INT truncates it to -2.
Reference: [2]
Error Handling: programming and short-response practice
39. Misspelled SAS statements
SAS syntax errors often point near the statement the compiler cannot recognize. Start with the first relevant ERROR in the log, then inspect its source line and the statement immediately before it. A misspelled keyword can make later tokens look invalid, creating several messages from one defect. SAS statements normally end with semicolons; a procedure begins with `proc` followed by its name and options. Correct the earliest malformed statement, rerun the program and read the new log rather than repairing every later message independently. Check that the corrected code produces the intended output as well as a clean syntax path. A step can compile yet still sort the wrong variable, so verify the BY key and resulting order. The log is the diagnostic starting point, while the resulting data confirms the repair.
Worked example: Given ORDERS with IDs 2 then 1, `prc sort data=orders; by id; run;` fails because `prc` is not the PROC keyword. Replace it with `proc sort data=orders out=ordered; by id; run;`. ORDERED then contains IDs 1, 2. The original ORDERS order remains 2, 1 because OUT= directs the sorted result elsewhere.
Watch the distinction: Treating every later log error as a separate typo wastes effort. Repair the first malformed keyword, rerun, and see which downstream messages remain.
Reference: [2]
40. Missing semicolons
A semicolon ends a SAS statement, not merely a printed line. If one is missing, SAS continues reading subsequent text as part of the same statement until it reaches a semicolon. The diagnostic may therefore appear on a later line even though the omission occurred earlier. When the log reports an unexpected name, option or token, inspect the preceding statement for its terminator. In a DATA step, `set sales;` reads each input observation, and a following assignment such as `total=price*qty;` calculates a value for that observation. Both statements need their own semicolons. Restore the missing terminator, rerun, inspect the log and check the resulting values. A clean log establishes that the statements parsed; the output check establishes that the calculation matches the intended multiplication.
Worked example: The code `data want; set sales total=price*qty; run;` has lost the semicolon after SALES, so SAS reads the assignment as part of SET syntax. Correct it to `data want; set sales; total=price*qty; run;`. For a SALES row with PRICE=5 and QTY=2, WANT has TOTAL=5×2=10.
Watch the distinction: Adding a semicolon only at the end of the assignment leaves SET and the assignment fused. Put the missing semicolon immediately after the input dataset name.
Reference: [2]
41. Unmatched quotation marks
SAS statements end with semicolons, but a semicolon inside a quoted string is data, not a statement terminator. If you write an opening quote without its matching closing quote, SAS may treat later lines as part of the string. The log can then show errors far below the real defect, including statements that appear to be missing or out of place. Start at the first relevant diagnostic and scan backward for a quote that never closes. Check whether the intended value uses matching single quotes or matching double quotes, then end the assignment with a semicolon. After repairing the earliest open quote, submit the step again and read the new log. The SAS exam content guide includes unmatched quotes among the syntax errors to recognize.
Worked example: Suppose the intended assignment is Open. The faulty line is `status='Open;`. Its semicolon remains inside the unfinished string. Correct it with `data work.flags; length status $4; status='Open'; output; run;`. The output has one observation with character STATUS equal to `Open`. The closing quote completes the value, and the following semicolon ends the assignment.
Watch the distinction: A tempting fix is to add a semicolon on the next line. That does not close the string; find and close the earliest unmatched quote first.
Reference: [2]
42. Invalid options in context
An option is valid only where its procedure's syntax permits it. In PROC SORT, `DATA=` names the input and `OUT=` names a separate output data set. Both belong on the PROC SORT statement. The BY statement names the sort keys; placing `OUT=` there asks SAS to interpret it in a context that does not accept that option. When the log reports an invalid or unexpected option, identify the owning statement and compare its job with the option's job. Avoid moving an option at random: first decide whether it controls the procedure, an input data set, an output data set, or a particular statement. In this case, moving `OUT=` preserves the source's observation order while creating a sorted copy.
Worked example: Start with ID values 2, then 1. Use `data work.raw; input id; datalines; 2 1 ;` with each number on its own data line, followed by `proc sort data=work.raw out=work.ordered; by id; run;`. ORDERED contains IDs 1, 2; RAW remains 2, 1. `by id out=ordered;` is misplaced syntax.
Watch the distinction: Do not assume an option works wherever its name sounds relevant. `OUT=` controls PROC SORT's output data set, so it belongs on the PROC statement.
Reference: [2]
43. Inspecting values with PUTLOG
`PUTLOG` writes diagnostic text to the SAS log during DATA step execution. Its position matters because the program data vector changes as statements run. A `PUTLOG` before an assignment shows the variable's current value; one afterward shows the calculated value for that iteration. Name variables with the `name=` form to label their values in the log, or use `_ALL_` to inspect the current values of all variables when the cause is unclear. You can also apply a format when the displayed form helps interpret a raw number, such as a SAS date. Keep diagnostic output close to the calculation under investigation so that the log shows which state you captured. Remove or restrict broad tracing after you locate the problem, since every input row can otherwise generate a log line.
Worked example: With PRICE=5 and QTY=2, run `data work.check; price=5; qty=2; putlog 'Before: ' total=; total=price*qty; putlog 'After: ' total=; run;`. The first log entry shows TOTAL missing because it has not been assigned. The second shows TOTAL=10, since 5 times 2 is 10. Moving the trace changes the observed state, not the calculation.
Watch the distinction: Seeing a missing TOTAL before its assignment does not prove multiplication failed. Check where `PUTLOG` occurs within the iteration before changing the formula.
Reference: [2]
44. Conditional debugging
Conditional logging narrows a large DATA step trace to observations that satisfy a diagnostic test. `_N_` is SAS's automatic DATA step iteration counter, so it helps locate the iteration that produced a suspicious value. It is not necessarily a source record ID: a step can read more than one source row per iteration or produce multiple output rows. Put the condition after the value you want to inspect has been read or calculated. A statement such as `if amount<0 then putlog _N_= amount=;` writes only for negative amounts. Use the same logic with impossible dates, unexpected categories, or totals outside an expected range. If the source has its own identifier, log that identifier too; it remains meaningful after sorting or merging.
Worked example: Read amounts 5, -2, and 8 with `data work.check; input amount; if amount<0 then putlog _N_= amount=; datalines; 5 -2 8 ;` with each amount on its own data line. The log records `_N_=2 amount=-2` once. Rows one and three pass the condition, so they produce no diagnostic message.
Watch the distinction: Do not interpret `_N_=2` as a permanent row identifier. It identifies the second DATA step iteration; retain or log a business key when tracing source records.
Reference: [2]
45. The _ERROR_ variable
`_ERROR_` is an automatic numeric DATA step variable that SAS sets when it detects certain execution errors, including invalid numeric input. It is reset for the next iteration, so test it while processing the affected record. Its value is a debugging signal, not a business quality rule. A syntactically valid but implausible quantity, such as a negative order size, may leave `_ERROR_` at zero; create a separate flag if your own rule should catch that case. When `_ERROR_` becomes one, inspect the first nearby log note and the raw input that produced it. Invalid numeric text normally becomes a numeric missing value, which may then spread through arithmetic. Logging `_N_`, the source identifier, and relevant raw fields gives you a reproducible path to the failure.
Worked example: Run `data work.qty; input quantity 8.; if _ERROR_ then putlog _N_= quantity= _ERROR_=; datalines; 12 XX ;` with `12` and `XX` on separate data lines. The first quantity is numeric 12. The second cannot be read as numeric, so QUANTITY is missing and the diagnostic line identifies `_N_=2` with `_ERROR_=1`.
Watch the distinction: A zero `_ERROR_` does not mean a record meets your business rules. SAS can read a negative quantity successfully even if your application rejects it.
Reference: [2]
Error Handling: multiple-choice practice
46. Syntax error versus logic error
A syntax error prevents SAS from interpreting a statement as intended; a logic error lets the program run but yields an unintended result. Their remedies differ. For syntax, inspect the first relevant log error, statement boundaries, keywords, quotes, and option placement. For logic, compare the output with a hand-calculated result from a small case and inspect intermediate values. A clean log does not prove the formula matches the task. For example, `price+qty` is valid SAS code, yet it does not compute sales value when the required calculation is price times quantity. The requirement itself must be clear; a business analysis guide can help frame what a calculated field is meant to represent. Then use test observations that distinguish competing formulas.
Worked example: Given PRICE=5 and QTY=2, sales value should be 5 times 2, or 10. `total=price+qty;` compiles and returns 7, exposing a logic error. A missing semicolon after `set work.sales` instead disrupts SAS statement parsing. Repair the terminator, then use `total=price*qty;` and check that the result is 10.
Watch the distinction: Do not equate an error-free log with a correct answer. SAS can execute a perfectly legal addition when the requested calculation requires multiplication.
Reference: [2]
47. Reading the first useful log message
One early syntax defect can produce several later log messages because SAS continues trying to parse the submitted program. An open quote is especially disruptive: later semicolons and keywords can be consumed as text, leaving following statements apparently malformed. Read the log in order, connect its first useful diagnostic to the preceding source, and inspect the smallest nearby region that could cause it. Fix that earliest cause before interpreting later messages as independent defects. Then submit the corrected code and read the fresh log from the beginning. This sequence prevents unnecessary edits to statements that were valid all along. A line number is a starting point, not always the precise location: the actual missing quote or semicolon may be on the previous line. The SAS Base Programming practice questions provide another setting for tracing such failures.
Worked example: Suppose a step contains `status='Open;` followed by `if status='Open' then flag=1;`. Later log messages may point at the IF statement because the first string never closed. Repair the earlier line to `status='Open';`, then rerun. With STATUS equal to Open, the IF condition is true and FLAG becomes 1.
Watch the distinction: Do not repair every line named by a cascade of syntax messages at once. Correct the earliest plausible defect and see which later messages remain.
Reference: [2]
48. Missing values as diagnostic evidence
A missing numeric result can have several causes. The source variable may legitimately be missing, an attempted conversion may have failed, or ordinary arithmetic may have propagated a missing operand. Diagnose those cases before changing the formula. In SAS, `a+b` returns missing if either operand is missing. `sum(a,b)` instead adds available nonmissing arguments, so one missing A and B=3 produce 3. If every argument is missing, `SUM` returns missing. Neither expression decides whether treating a missing value as zero is appropriate for the task. Check the raw observation, variable type, log notes, and intended meaning of absence. Character missing values are blank strings, while numeric missing values display as a dot by default; confusing those types can send debugging toward the wrong operation.
Worked example: Set A to numeric missing and B to 3. `data work.check; a=.; b=3; ordinary=a+b; available=sum(a,b); run;` yields ORDINARY missing and AVAILABLE=3. The first expression requires both operands; the second uses the observed B. If A meant an unrecorded amount rather than zero, AVAILABLE=3 needs a cautious interpretation.
Watch the distinction: Replacing every missing result with zero hides its cause. First determine whether the source was absent, an input conversion failed, or the formula propagated missing data.
Reference: [2]
49. Tracing DATA step iterations
A DATA step normally writes one observation at the end of each iteration, but an explicit `OUTPUT` statement controls when rows are written. Once a step contains explicit `OUTPUT` statements, do not count on an additional implicit row at the end. Trace three separate counts: input observations read, DATA step iterations, and output observations written. `_N_` counts iterations, not writes. A single iteration can produce zero, one, or several rows, depending on conditions and `OUTPUT` placement. To diagnose an unexpected row count, mark each `SET` read and each reachable `OUTPUT` in program order, then calculate how many writes occur for each iteration. Include a source key if you need to distinguish rows that share the same `_N_`. A short `PUTLOG` just before each output can reveal the exact path taken.
Worked example: With two source rows, run `data work.twice; set work.source; output; output; run;`. For the first input row, `_N_=1` during both writes; for the second, `_N_=2` during both writes. Two inputs times two explicit writes give four output rows. There is no fifth or sixth row from an additional implicit write.
Watch the distinction: Do not treat `_N_` as the output row number. Two explicit `OUTPUT` statements can write two observations while `_N_` still has one value.
Reference: [2]
Generate Reports and Output: programming and short-response practice
50. Focused list reports
PROC PRINT makes a detail report from observations in a SAS data set. `WHERE` selects source observations that meet a condition, `VAR` chooses and orders displayed columns, and `NOOBS` removes the default observation-number column. Decide first which rows are relevant, then which fields a reader needs, and finally whether row numbers add value. The report does not change the underlying data set. A `WHERE` condition works on existing variables and can reduce rows before they reach the procedure; it does not create a new measure. If you need a calculated field, create it in a DATA step or an appropriate procedure before printing. Check both row count and displayed headings, since a correct filter can still leave distracting columns. For a threshold test, state whether equality is included: `sales>10` excludes 10.
Worked example: Given SALES values 5, 12, and 18, use `proc print data=work.sales noobs; where sales>10; var sales; run;`. The report has a SALES heading and two displayed rows: 12 and 18. Five fails the strict greater-than condition. `NOOBS` suppresses observation numbers; it does not remove either qualifying data row.
Watch the distinction: Do not use `VAR` to filter rows. It controls columns only; `WHERE sales>10` performs the observation selection in this report.
Reference: [2]
51. Identified and totaled reports
PROC PRINT can display detail rows and totals in one report. `ID` uses a meaningful variable to identify each row in place of the default observation number. `BY` creates separate group sections and requires the data to be ordered by the BY variables; sort first unless the data already meet that requirement. `SUM` totals the named numeric variable within each BY group and may also show an overall total, depending on the output layout. `LABEL` tells PROC PRINT to use variable labels as headings. `SPLIT=` identifies a character in a label where the heading should break across lines. Keep stored variable names for code and descriptive labels for readers. When checking the report, separate row values from the displayed subtotal so you do not count the subtotal as an additional observation.
Worked example: For sorted rows `(A,101,4)` and `(A,102,6)` as REGION, ORDER_ID, SALES, run `proc print data=work.orders label split='*'; by region; id order_id; var sales; sum sales; label sales='Sales*amount'; run;`. The A section identifies orders 101 and 102, shows 4 and 6, and totals SALES at 10. The heading breaks at `*`.
Watch the distinction: Do not apply `BY region` to unsorted data and expect class-style grouping. PROC PRINT needs BY order; `CLASS` in summary procedures follows different rules.
Reference: [2]
52. Frequency tables as validation
PROC FREQ counts categories and combinations of categories, making it useful for checking encoded data. A one-way table shows each distinct formatted value of one variable, its frequency, and its percentage. A two-way table, requested with `tables variable1*variable2;`, exposes combinations that may be implausible even when each individual value looks valid. `NLEVELS` reports how many levels occur. `ORDER=` controls the display order, such as data order rather than the default ordering; it does not change stored values. A spelling variant creates another level, so unexpected categories often reveal input or recoding defects. Check missing values too: default tables generally exclude them from percentage calculations unless you request their inclusion with the appropriate table option. Use the counts as evidence about the data, then trace suspicious categories to source rows.
Worked example: For STATUS values Open, Open, and Closed, `proc freq data=work.tickets nlevels order=data; tables status; run;` reports two levels: Open with frequency 2 and Closed with frequency 1. If a fourth row contains `Opne`, NLEVELS becomes 3 and a separate frequency-1 category appears. `tables status*channel;` would inspect status-channel combinations.
Watch the distinction: Do not assume `ORDER=DATA` cleans category values. It only changes presentation order; `Opne` remains a separate level until the data are corrected.
Reference: [2]
53. Grouped summary statistics
PROC MEANS summarizes numeric variables. `VAR` selects measures, `CLASS` names categorical grouping variables, and `WAYS` selects how many class variables participate in each summary level. With one CLASS variable, `WAYS 0 1` requests an overall result and one result per class level. CLASS processing forms groups without requiring BY sorting. `OUTPUT OUT=` stores statistics in a SAS data set for later checks or reports; name the statistics you need so the output columns are clear. The overall mean is based on all nonmissing measurements, not an unweighted average of the class means when class sizes differ. If some groups contain missing measurements, compare each output count with its source group size. `_TYPE_` in the output identifies the summary level, while `_FREQ_` records contributing observations under the procedure's rules.
Worked example: For REGION A with SALES 4 and 6, and B with SALES 3, use `proc means data=work.sales mean noprint; class region; ways 0 1; var sales; output out=work.summary mean=mean_sales n=n_sales; run;`. A's mean is `(4+6)/2=5`; B's is `3/1=3`. The overall mean is `(4+6+3)/3=13/3`, about 4.33.
Watch the distinction: Do not average 5 and 3 to claim an overall mean of 4. The class sizes differ, so the overall calculation uses all three observations.
Reference: [2]
54. Finding extremes and missing data
PROC UNIVARIATE provides distribution details beyond a simple mean, including extreme observations and missing-value information. Use `VAR` to restrict analysis to the measure of interest. The procedure computes location and spread from nonmissing numeric values; the number of missing observations is reported separately. Inspecting extremes helps detect entry errors, unit mismatches, or genuine unusual cases. An extreme is a diagnostic cue, not automatic proof that a value is wrong. Compare its source observation and expected measurement range before excluding it. The minimum and maximum provide a quick boundary check, while the nonmissing and missing counts tell you which observations contributed to calculated statistics. For repeatable reviews, record the variable and source population used; changing a filter can change both the extremes and the missing count.
Worked example: For MEASURE values 2, 5, 20, and numeric missing, run `proc univariate data=work.readings; var measure; run;`. The procedure analyzes three nonmissing observations and reports one missing value. The minimum is 2 and the maximum is 20. The value 20 is the observed high extreme, which merits checking against its source and units.
Watch the distinction: Do not divide by four when interpreting statistics based on these measurements. The missing observation is counted separately and does not supply a numeric value.
Reference: [2]
55. Custom value formats
A SAS format controls how a stored value appears in output; it does not rewrite the underlying value. PROC FORMAT defines a user format with a `VALUE` statement. Apply that format to a variable in a DATA step or reporting procedure, and numeric codes can appear as readable categories. This helps reports while preserving numeric codes for comparisons and storage. When many mappings already exist as data, `CNTLIN=` reads a control data set rather than requiring a long hand-written VALUE statement. That control data set supplies fields such as format name, starting value, label, and type. Inspect the stored codes and the formatted display when debugging: a surprising label could reflect a wrong code or a wrong mapping. Also remember that a format must be available in the session where it is used.
Worked example: Use `proc format; value level 1='Low' 2='High'; run; proc freq data=work.ratings; tables code; format code level.; run;`. For two observations with CODE 1 and 2, the table displays Low and High, each with frequency 1. The stored numeric codes remain 1 and 2, so `if code=2` still selects High.
Watch the distinction: Do not compare numeric CODE to the displayed text `High`. The format changes presentation; the stored value used by the comparison is still 2.
Reference: [2]
56. Routing reports with ODS
The Output Delivery System routes procedure results to rendered destinations. Open a destination before the procedure, supply its file and optional `STYLE=` template, then close that destination after the output is produced. HTML, PDF, RTF, XLSX, and PPTX destinations serve different viewing or editing needs. ODS can also create CSV procedure output, but that is a report table rendered as delimited text, not necessarily a clean export of every observation and variable in a data set. Choose the destination based on what the recipient needs to read. Closing it matters because the destination must finish writing its file. If a report is empty, first verify that the procedure generated an output object and that filters did not remove all rows. The SAS Base Programming certification page identifies reports and output as part of the credential's scope.
Worked example: For a two-row WORK.SALES data set, run `ods pdf file='sales.pdf' style=journal; proc print data=work.sales noobs; var sales; run; ods pdf close;`. The PDF contains a PROC PRINT table with two SALES rows. The style changes presentation, while `NOOBS` removes observation numbers. Closing PDF completes the output file.
Watch the distinction: Do not leave the PDF destination open and assume the file is complete. Close it after the report so SAS finalizes the document.
Generate Reports and Output: multiple-choice practice
57. Report output versus data export
First identify whether the recipient needs a rendered report or the underlying rectangular data. ODS captures procedure output such as a frequency table, including its counts, percentages, headings, and styling. PROC EXPORT writes observations and variables from a SAS data set into a file another application can read. Those are different objects even when both use a familiar extension. A CSV export of a two-column data set usually has a header and one record per observation, subject to options such as `PUTNAMES=`. An ODS CSV destination instead writes selected procedure tables as CSV, potentially including summary structures rather than the original rows. Choose by the intended use: review a formatted result with ODS, or transfer data for further calculation with an export method. Verify the receiving application's expected column names and types.
Worked example: A formatted STATUS frequency table belongs in ODS output because the reader needs counts and percentages. For data rows `(A,4)` and `(B,6)` in columns ID and VALUE, `proc export data=work.values outfile='values.csv' dbms=csv replace; run;` produces a header plus two data records: A,4 and B,6. The export contains observations, not frequency statistics.
Watch the distinction: Do not send an ODS frequency table when the recipient expects raw rows. The table's counts are derived results and cannot substitute for the original observations.
Reference: [2]
58. Export file formats
PROC EXPORT writes a SAS data set to an external format, and `DBMS=` selects the target type. `CSV` uses comma-delimited fields, `TAB` uses tab-delimited fields, `DLM` supports a specified delimiter, and `JMP` targets a JMP data file. Select the identifier from the file specification, not from the current SAS data set's name. `OUTFILE=` names the target file; `REPLACE` permits overwriting an existing target. Before exporting, decide which variables and observations should be present, because PROC EXPORT writes the chosen input data set rather than a procedure's displayed report. Afterward, check the header, record count, delimiter, and how character values containing a delimiter were quoted. A CSV file is plain text, so display formats and type information may not survive as native SAS attributes in the receiving application.
Worked example: For ID, VALUE rows `(1,5)` and `(2,7)`, run `proc export data=work.measurements outfile='measurements.csv' dbms=csv replace; run;`. With default column names, the file contains `ID,VALUE`, then `1,5`, then `2,7`: one header and two data records. The commas separate fields; they do not change the numeric values.
Watch the distinction: Do not choose `DBMS=TAB` merely because a spreadsheet will open the file. Use `DBMS=CSV` when the required delimiter is a comma.
Reference: [2]
59. Writing an Excel sheet
The XLSX LIBNAME engine exposes an Excel workbook as a SAS library. Workbook sheets appear as members, so writing `libref.sheetname` creates or updates a sheet through the engine. Assign the libref to the workbook path, write a SAS data set to the desired member, and clear the libref when finished so the workbook connection closes cleanly. The member name identifies the sheet target; it is not another directory or a separate SAS data file. Variable names become column headings, and observations become data rows. Check type and display expectations in Excel, particularly for dates and character identifiers with leading zeros. A sheet can have workbook-specific constraints, so verify the resulting workbook when the downstream use depends on exact formatting or formulas. This method differs from sending a PROC PRINT report to ODS XLSX.
Worked example: Assume WORK.SUMMARY contains two observations with ID and VALUE. Use `libname book xlsx 'summary.xlsx'; data book.totals; set work.summary; run; libname book clear;`. The workbook's TOTALS sheet receives the two observations and their two variable columns. Clearing BOOK closes the connection after the sheet has been written.
Watch the distinction: Do not mistake `book.totals` for a separate workbook named TOTALS. BOOK refers to the workbook; TOTALS names a sheet within it.
Reference: [2]
60. Titles, footnotes and headings
Titles and footnotes give a report context beyond its data columns. `TITLE` places text above procedure output, while `FOOTNOTE` places explanatory text below it; numbered versions let you manage multiple lines. A variable label is a descriptive attribute attached to a variable, whereas the variable name remains the identifier used in code. PROC PRINT's `LABEL` option displays labels as column headings when labels exist. With `SPLIT=`, a chosen character in a label marks where a long heading breaks. None of these presentation controls changes numeric values or the arithmetic of a `SUM` statement. Set the title and footnote before the procedure, then clear them when later output should not inherit that context. Inspect the final report for ambiguous units, especially when a total could mean currency, counts, or another measure.
Worked example: For AMT values 4 and 6, run `title 'Q1 Results'; footnote 'Amounts in units'; proc print data=work.q1 label; var amt; sum amt; label amt='Net sales'; run; title; footnote;`. The heading reads Net sales, the detail values are 4 and 6, and the total is `4+6=10` units. Clearing TITLE and FOOTNOTE prevents carryover.
Watch the distinction: Do not expect `label amt='Net sales'` to rename AMT in code or alter its value. It changes the displayed heading when labels are requested.
Reference: [2]