SAS interviews should reveal whether candidates can transform data without losing its meaning. Recruiters need to assess observation grain, variable attributes, missing values, DATA step execution, joins and validation. A program that finishes successfully can still truncate identifiers, multiply measures or carry stale state into the next group.
This guide to 100+ SAS Interview Questions and Answers contains 120 questions across beginner, intermediate and experienced levels. It covers Base SAS programming, PROC SQL, macros, reporting and reliable pipelines, with explicit distinctions between conventional Compute processing and distributed CAS execution in SAS Viya.
How can recruiters verify genuine SAS debugging ability when a candidate can recite syntax but cannot explain an unexpected row count?
Ask candidates to trace a small input through the program data vector, a merge or a macro-generated step, then introduce a duplicate key or missing value. Someone who understands SAS should explain where an observation comes from, when a value is retained and why a row count changes. That reasoning provides stronger evidence than a clean log or a memorized procedure name alone.
Sherlock AI helps hiring teams assess signals of potential AI cheating, deepfakes, impersonation and unauthorized assistance during remote interviews. Use those signals alongside practical debugging exercises and the candidateโs explanation of the data. They provide context for a fair review rather than conclusive proof of misconduct.
SAS Interview Questions
Choose questions that reflect the roleโs data and execution environment. Begin with observation-level reasoning, then move to joins, macro timing and production diagnosis. Each question has a matching numbered answer below; a focused selection provides more useful evidence than a rapid syntax quiz.
SAS Interview Questions for Beginners
Establish the language, metadata, missing-value and execution foundations needed to read and explain ordinary SAS programs.
What is SAS used for in a data workflow?
How do DATA steps and PROC steps differ?
What are the descriptor and data portions of a SAS data set?
What are observations and variables?
How do temporary and permanent SAS libraries differ?
What does a two-level SAS data set name mean?
What does the LIBNAME statement do?
What are the principal Base SAS variable types?
How does a character variable's length affect data?
How does a format differ from an informat?
How do variable labels differ from variable names?
Why are semicolons important in SAS programs?
What do RUN and QUIT do?
What information should you inspect in the SAS log?
What happens during DATA step compilation?
What is the program data vector?
What does the automatic variable _N_ represent?
What does the automatic variable _ERROR_ indicate?
How are missing numeric and character values represented?
How are SAS dates stored?
How do SAS time and datetime values differ from dates?
How do SAS date literals work?
How does IF-THEN/ELSE support conditional logic?
How does a subsetting IF filter observations?
How does WHERE differ from a subsetting IF?
How do KEEP and DROP affect variables?
What does the RENAME option help accomplish?
How does SET concatenate data sets?
What does PROC SORT do?
What are FIRST.variable and LAST.variable?
What is the purpose of RETAIN?
How does a sum statement differ from ordinary addition?
What is a SAS array?
How do iterative DO loops work?
How do DO WHILE and DO UNTIL differ?
What does OUTPUT change in a DATA step?
What does DELETE do in a DATA step?
How do PROC PRINT and PROC CONTENTS differ?
What are PROC MEANS and PROC FREQ commonly used for?
How does a macro variable differ from a DATA step variable?
SAS Interview Questions for Intermediate
Use transformation and debugging questions to examine grouping, joins, conversion, reporting and macro timing with concrete inputs.
How would you import a CSV file reproducibly?
What do DSD, DLM and TRUNCOVER control during input?
When should you use the INPUT and PUT functions?
Why is implicit type conversion risky?
How do CAT, CATS, CATT and CATX differ?
How do SCAN and SUBSTR solve different string tasks?
How do INDEX and FIND differ in string searching?
What does the LAG function actually return?
How can you compute a previous value within each BY group?
How would you calculate a running total within each customer?
How does a DATA step match-merge differ from a SQL join?
What does the IN= data set option tell you during a merge?
How would you produce unmatched records from two sources?
Why should duplicate keys be checked before merging?
What does NODUPKEY remove in PROC SORT?
How would you select the latest record per identifier?
How do WHERE and HAVING differ in PROC SQL?
How do COUNT(*) and COUNT(column) differ?
Why can PROC SQL remerge summary statistics?
How can a LEFT JOIN accidentally behave like an INNER JOIN?
How does CALCULATED work in PROC SQL?
How do UNION and UNION ALL differ?
When would you use EXISTS instead of a join?
How do SQL pass-through and SAS-side SQL differ?
How does PROC TRANSPOSE reshape data?
How do CLASS and BY processing differ in summary procedures?
What does the NWAY option do in PROC SUMMARY?
How can ODS OUTPUT help build reproducible reports?
How would you compare two SAS data sets?
What does PROC APPEND trade off against rebuilding with SET?
How do local and global macro variable scopes differ?
How do positional and keyword macro parameters differ?
Why do %EVAL and %SYSEVALF behave differently?
What is macro quoting used for?
How does %SUPERQ help handle macro variable contents?
How does CALL SYMPUTX transfer data to the macro facility?
How does SYMGET differ from a macro variable reference?
What timing issue can CALL EXECUTE introduce?
How do MPRINT, MLOGIC and SYMBOLGEN assist debugging?
How would you validate a macro parameter before use?
SAS Interview Questions for Experienced
Evaluate reliability, migration, performance, governance and recovery decisions. Strong answers state the environment and test the assumptions behind the proposed solution.
How would you investigate an unexpectedly small output table?
How can a program finish without errors yet produce wrong results?
How would you design a rerunnable SAS pipeline?
How do you distinguish a checkpoint from a trustworthy restart point?
How would you handle schema drift in incoming data?
How would you diagnose character truncation across a pipeline?
Why can encoding migration change character-processing results?
How would you define reliable missing-data handling?
What can cause many-to-many joins to inflate totals?
How would you reconcile a DATA step merge with a database join?
How would you choose between sorting and indexed access?
When is an in-memory hash object useful in a DATA step?
How can stale values affect hash lookup output?
How would you assess compression for a SAS data set?
How would you reduce unnecessary I/O in a SAS job?
How can you verify that SQL work was pushed to a database?
Why can pushing a query to a database change results?
How would you benchmark two SAS implementations fairly?
How does CAS execution differ from a conventional Compute DATA step?
Why can retained accumulators give unexpected results in CAS?
What ordering assumptions should you revisit when moving to CAS?
How do session-scoped and promoted CAS tables differ?
How would you decide whether a workload belongs in CAS?
How would you migrate a SAS 9 workflow to Viya safely?
How would you secure credentials used by SAS jobs?
How can dynamically generated SAS code create a security risk?
How would you test a reusable macro library?
What makes a useful SAS regression test?
How would you validate floating-point results?
How would you preserve data lineage in a SAS pipeline?
How would you handle late-arriving or corrected source records?
How would you make a large append process recoverable?
How should a SAS pipeline handle zero-row inputs?
How would you investigate a program that hangs or runs indefinitely?
How can session state undermine reproducibility?
How would you manage user-defined formats as dependencies?
What should an operational quality gate check before releasing a result?
How would you schedule dependent SAS jobs reliably?
How would you design diagnostic logging without exposing sensitive data?
How would you review a senior SAS developer's solution?
SAS Interview Questions and Answers
The answers primarily describe traditional Base SAS DATA step and PROC SQL behavior in SAS 9.4 or SAS Compute. CAS and database-engine differences are called out where material. A correct explanation identifies where code runs, what one observation represents and which attributes or values persist between iterations.
SAS Interview Questions and Answers for Beginners
These answers connect syntax to the observation and its metadata. Look for explanations of numeric storage, character length, missing values and the DATA step cycle that predict what a small input will produce.
1. What is SAS used for in a data workflow?
SAS provides a programming language and analytical tools for importing, transforming, validating, analyzing and reporting data. A typical workflow combines DATA steps with procedures. In an interview, identify the actual environment: Base SAS on a SAS Compute server and distributed processing in CAS have different execution assumptions, even when some syntax looks familiar.
For example, a monthly sales workflow might read a source extract, standardize customer identifiers, reject invalid amounts, summarize sales by region and produce a report. The transformation is only one part of the work: reconciling input and output counts and documenting rejected records are equally important. I would explain where the data lives and which server executes each step before proposing performance changes.
2. How do DATA steps and PROC steps differ?
A DATA step provides observation-oriented programming for reading and transforming data, while a PROC step invokes a procedure for a defined task such as sorting, summarizing or reporting. Some procedures also create or modify tables. Choose based on the required behavior and available engine, rather than assuming that every transformation belongs in a DATA step.
To calculate a row-level margin, a DATA step can read each sale and assign margin = revenue - cost;. To obtain the mean margin by region, PROC MEANS expresses the summary directly. Replacing a well-suited procedure with a manual accumulation loop increases the amount of grouping and missing-value logic the programmer must maintain. Conversely, a procedure is not automatically clearer for complex stateful row processing.
3. What are the descriptor and data portions of a SAS data set?
The descriptor portion records metadata such as variable names, types, lengths, labels and formats. The data portion holds observations. Inspect both when diagnosing unexpected output: a value can be truncated or displayed misleadingly because of metadata even when the transformation logic appears correct. PROC CONTENTS is a useful starting point for this inspection.
If an account identifier displays without leading zeros, I would check whether it was imported as numeric rather than merely changing its label. If a customer name is cut off, I would inspect the character length in PROC CONTENTS and compare it with the source. Printing a few rows shows symptoms; examining attributes explains why the same problem can affect every later observation.
4. What are observations and variables?
An observation is a row representing an entity or event, and a variable is a column with a consistent type and attributes. The unit represented by each row is its grain. Establish that grain before joins or aggregation; a customer-level table and a transaction-level table can share a customer identifier while having very different valid row counts.
A table with one row per order can legitimately contain several rows for one customer. Joining it to a customer table should preserve that order grain when customer IDs are unique on the customer side. Joining to another table with multiple rows per customer can multiply orders. Before calling an increased count a bug or using deduplication to hide it, I would state what one output observation is supposed to represent.
5. How do temporary and permanent SAS libraries differ?
WORK normally contains session-scoped temporary data, whereas an assigned persistent library points to storage intended to survive the session. A libref is a logical name, not the physical data itself. Verify engine, path and access permissions. Persistence also depends on the actual deployment and storage lifecycle, so a directory assignment alone is not a backup policy.
A useful pattern is to keep intermediate extracts and diagnostic samples in WORK while writing an approved final result to an explicitly assigned output library. A restart cannot normally rely on a previous session's WORK tables. If a long job needs checkpoints, those checkpoints require durable storage and a documented lifecycle; naming a temporary table โfinalโ does not make it permanent.
6. What does a two-level SAS data set name mean?
A two-level name combines a libref and member name, such as work.orders. The libref identifies an assigned library and its engine, while the member identifies the data set within it. Using explicit two-level names improves clarity in reusable code and reduces dependence on session defaults that might direct an unqualified name to an unintended library.
In work.orders, work is the library and orders is the member. A one-level name often resolves to WORK, but session configuration such as a USER library can change that behavior. Explicit names are particularly useful when a program reads source.orders and creates work.orders: the code makes clear that the transformation is not intended to overwrite its source.
7. What does the LIBNAME statement do?
LIBNAME associates a libref with a data source through an engine and connection options. The source might be a filesystem library or a database, depending on installed products and authorization. It does not necessarily copy data into SAS. Keep credentials out of source code and understand which operations the selected engine executes locally or remotely.
For a filesystem example, libname stage '/approved/project/stage'; assigns a logical name to an existing accessible location; it does not create a copy of every file there. With a database engine, the same libref/member pattern can represent remote tables. I would verify the engine and connection policy before assuming that a sort, filter or write has the same cost and permissions as a local SAS data set.
8. What are the principal Base SAS variable types?
Traditional SAS data sets primarily use numeric and fixed-length character variables. Dates and times are normally numeric values with associated formats, rather than separate storage types. Other SAS products and engines can expose additional capabilities. Distinguish a variable's stored type from its displayed representation before converting values or comparing schemas across environments.
A postal code such as 00125 is usually an identifier and should be character data, even though it contains digits. An amount that will be summed should be numeric after explicit parsing. Storing an identifier as numeric can lose meaningful leading zeros, while storing amounts as character requires conversion before arithmetic. A format can change appearance, but cannot recover information already lost during ingestion.
9. How does a character variable's length affect data?
Length determines the allocated storage width for a character variable in a traditional SAS data set. If a value exceeds that width, information can be truncated. Establish an adequate length before the variable is first defined during compilation. In a multibyte encoding, bytes and visible characters may differ, so cross-encoding migration needs extra attention.
If the first assignment establishes status from the literal 'New', a later longer value may not fit the resulting character width. Declaring length status $12; before those assignments establishes the intended capacity. The same issue can arise when combining tables whose name columns have different lengths. Increasing the length after truncation has already occurred cannot restore the missing characters.
10. How does a format differ from an informat?
An informat tells SAS how to interpret incoming text or other encoded values; a format tells SAS how to display stored values. Neither should be confused with the underlying variable type. Reading a date with an informat produces a numeric date value, while assigning a date format changes its presentation without changing the stored number.
For text 01NOV2026, the DATE9. informat interprets the day, month and year; a YYMMDD10. format can then display the stored date as 2026-11-01. Applying a date format directly to the character text does not parse it. The separate roles matter when a source changes its layout: update and validate the input interpretation rather than adjusting only the report's appearance.
Role | Informat | Format |
|---|---|---|
Purpose | Interpret incoming values | Control displayed values |
Date example | Read 01NOV2026 with DATE9. | Display the date as 2026-11-01 with YYMMDD10. |
What it does not do | Choose the intended source convention for you | Repair an incorrectly parsed stored value |
11. How do variable labels differ from variable names?
A variable name identifies the column in code, while a label supplies descriptive text for output and metadata. Labels improve reports without forcing long or awkward programming identifiers. Do not use a displayed label as evidence that two columns have identical semantics; compare names, types, definitions and the business meaning of the stored values.
A column named net_amt can carry the label โNet sales amount (USD)โ while code continues to reference net_amt. That makes units visible without changing downstream programs. A label is still documentation, not an enforced currency rule: if the source contains several currencies, the workflow must convert or group them correctly rather than imply consistency through a friendly heading.
12. Why are semicolons important in SAS programs?
Semicolons terminate most SAS statements, allowing a statement to span lines and multiple statements to share a line. A missing semicolon can cause later text to be parsed as part of an earlier statement, producing misleading errors downstream. When a log looks confusing, inspect the first error and the preceding statement rather than fixing only the last message.
For example, forgetting the semicolon after length customer $20 can make the following set statement appear to be part of the declaration. The reported error may therefore point later than the actual mistake. I would first check statement boundaries, quotes and comments near the earliest error, rerun the corrected small section, then assess whether remaining messages are independent problems.
13. What do RUN and QUIT do?
RUN executes or ends a step as appropriate to the procedure, while QUIT terminates procedures that remain active until explicitly closed, including PROC SQL. Their behavior is procedure-specific rather than interchangeable. Use clear step boundaries so generated code, macro variable timing and output destinations behave predictably, especially in programs that alternate DATA and PROC processing.
A clear SQL block ends with quit;, while an ordinary DATA step conventionally ends with run;. This distinction also matters when the step creates a macro variable for a later statement: the executing step must finish before later macro resolution can reliably use the new value. Consistent boundaries make the execution sequence visible instead of depending on the next step to terminate the previous one implicitly.
14. What information should you inspect in the SAS log?
Inspect errors, warnings and relevant notes, including input/output row counts, invalid data, implicit conversions, uninitialized variables and merge or SQL remerge messages. A completed job can still contain incorrect results. Pair log checks with data assertions, and preserve enough context to locate the earliest causal issue instead of treating all notes as either harmless or fatal.
If an expected 10,000-row extract produces 9,998 rows, I would trace the count through input, filtering and output rather than accepting a successful completion code. A note about invalid numeric data may explain new missing values; a conversion note may reveal a schema mismatch. Useful validation separates expected exclusions from unexpected losses and treats the log as diagnostic evidence alongside checks of the resulting data.
15. What happens during DATA step compilation?
SAS checks syntax and determines metadata needed to execute the step, including the program data vector and variable attributes. The order in which variables are first defined can affect type and length. Compilation does not mean every row has been processed successfully; execution can still encounter invalid input, missing values or conditions that produce unexpected output.
A LENGTH statement before a SET statement can establish a character width deliberately rather than inheriting an unsuitable width from the input. A branch that rarely executes can still affect attributes because compilation determines the variable layout before ordinary row processing. I would therefore inspect statement order when truncation occurs, rather than assume the first runtime value alone controls all metadata.
16. What is the program data vector?
The program data vector, or PDV, is the working area holding variable values while a DATA step processes observations. Statements read and modify these values before output is written. Understanding initialization and retention explains many SAS-specific bugs: variables created in the step commonly reset, while variables read by SET or MERGE have retention behavior that needs separate consideration.
Suppose flag is created by an assignment only when amount > 100. Without retention, it normally resets to missing on the next iteration; it does not automatically remain true. By contrast, a variable read by MERGE is marked for retention, which can preserve a changed value within a BY group when no new source observation replaces it. A PDV trace makes this distinction visible instead of treating every variable as if it resets identically.
17. What does the automatic variable _N_ represent?
_N_ counts iterations of the DATA step, beginning at one. It is not inherently the output observation number because one iteration can write zero, one or multiple observations. It is not written to the output data set automatically. Distributed execution changes the scope of processing, so do not treat it as a universal global row identifier in CAS.
If the first input observation is discarded and the second is written twice using explicit OUTPUT, both written rows can have the same iteration count if it is copied into a stored variable. That is why row_id = _N_; is not a general unique-output-row solution. If unique output numbering is required, maintain a counter at the actual output point or use a suitable key from the data contract.
18. What does the automatic variable _ERROR_ indicate?
_ERROR_ signals certain DATA step execution problems, such as invalid data during input. It helps diagnose the current iteration but is not a complete job success flag. It is not automatically included in the output data set. A robust workflow also checks the log, procedure outcomes and data-quality conditions that might not set _ERROR_.
When a numeric input field contains unexpected letters, SAS can set _ERROR_ and write diagnostic information to the log. A developer can use the flag to capture the offending input for investigation. But an incorrect business rule, such as including cancelled orders in sales, may produce no execution error at all. Setting _ERROR_=0 changes the flag; it does not repair the source value or prove the observation is valid.
19. How are missing numeric and character values represented?
A standard numeric missing value is represented by a period, with special numeric missing values also available. A character missing value is blank. Missing is not the same as zero or an empty business category. Use MISSING or suitable missing-value tests consistently, and define whether special missing codes carry distinct meaning in the data contract.
In conventional SAS numeric comparisons, missing values sort below ordinary numbers. Consequently, if amount < 0 can also select missing amounts, which is often not the intended โnegative amountโ rule. Use an explicit nonmissing test when that distinction matters. The MISSING() function recognizes ordinary and special numeric missing values as well as character missing values, avoiding a test that covers only the period code.
20. How are SAS dates stored?
A SAS date counts days relative to January 1, 1960; earlier dates can have negative values. A date format makes the number readable but does not alter that number. Parse source dates with the intended informat and validate ambiguous strings. A number that happens to display as a plausible date is not proof it was parsed correctly.
In this fixture, event_date and day_number contain the same numeric day count. Only event_date has DATE9. attached, so printing the two variables shows a readable date beside its underlying number. Adding one to a SAS date advances one day; it does not mean one month. Calendar-month arithmetic needs an appropriate interval function and an explicit alignment rule.
SAS example
21. How do SAS time and datetime values differ from dates?
A SAS time uses seconds within a day, and a SAS datetime uses seconds relative to January 1, 1960. A date uses days instead. Comparing dates directly with datetimes therefore mixes units. Convert deliberately with functions such as DATEPART or DHMS and define timezone handling in the source contract rather than assuming a display format performs conversion.
For an event recorded as a datetime, datepart(event_timestamp) obtains its date portion so it can be compared with a date-valued reporting boundary. Applying DATE9. directly to the seconds count does not divide by the number of seconds in a day. Likewise, converting to a date intentionally loses time-of-day precision, so filtering a partial-day reporting window requires datetime boundaries instead.
22. How do SAS date literals work?
A date literal such as '01NOV2026'd is parsed as a numeric SAS date constant. The suffix identifies the intended literal type; datetime and time literals use their corresponding forms. Literals are useful for clear comparisons in fixtures, but production parameters still need validation. Quoting an ordinary string without the proper suffix does not make it a date value.
For example, if event_date >= '01NOV2026'd; compares numeric dates using a readable constant. By contrast, '01NOV2026' alone is character text and can trigger unwanted conversion in a numeric comparison. Date literals are convenient for a known boundary; an externally supplied string still needs parsing and validation rather than being inserted uncritically into generated SAS code.
23. How does IF-THEN/ELSE support conditional logic?
IF evaluates a condition and executes its associated statement when true; ELSE handles the alternative. An ELSE IF chain selects the first matching branch, while separate IF statements can execute multiple branches. Make missing-value handling explicit and use DO/END for multiple statements. Indentation helps readability but does not determine which statements belong to a branch.
Consider categories for a numeric amount. Missing should be handled first, followed by negative, zero and positive values in an ELSE IF chain. In traditional SAS, a missing numeric value compares below zero, so starting with a simple negative comparison can misclassify it. For a branch that assigns both a status and a reason, put both statements inside DO; and END;; indentation alone does not group them.
24. How does a subsetting IF filter observations?
A subsetting IF stops processing the current DATA step iteration when its condition is false, preventing the default output for that iteration. It evaluates values available at that point, including calculated variables. Its position matters: statements before it may already have updated retained state or executed queue functions. Filtering is therefore not always equivalent to moving the condition earlier.
Suppose running + amount; occurs before if approved=1;. Rejected observations still contribute to the retained accumulator even though their rows are not written. Moving the filter before the sum changes the calculation. Neither order is universally correct: one might intentionally display an all-record total only on approved rows, while another requires totals of approved records only. The business definition determines the order.
25. How does WHERE differ from a subsetting IF?
WHERE selects observations from an input data source before they enter ordinary DATA step processing, whereas a subsetting IF executes within the step. WHERE can enable source-side filtering but cannot use a newly calculated DATA step variable. The timing can change BY-group and stateful calculations, so verify semantic equivalence before replacing one form with the other.
For margin = revenue - cost; followed by if margin > 0;, the calculated variable exists when the subsetting IF executes. A WHERE condition cannot refer to that newly created variable as though it were an input column. Filtering on an existing input field with WHERE may reduce I/O, but removing observations before BY processing can also change which surviving observation is marked first or last.
26. How do KEEP and DROP affect variables?
KEEP specifies variables to retain and DROP specifies variables to exclude. Their effect depends on whether they are statements or data set options on an input or output. Dropping at input can make a variable unavailable for calculations; dropping at output can remove it from the result while still allowing intermediate use. Place the restriction at the intended boundary.
If a result needs only customer and margin, data work.result(keep=customer margin); can remove revenue and cost from output while leaving them available for calculation. Putting a restrictive KEEP option on the input SET must still include those calculation inputs. A smaller output schema and a smaller input schema are separate decisions, even if both use the same option name.
27. What does the RENAME option help accomplish?
RENAME changes variable names at an input or output boundary, depending on where it is applied. It can avoid collisions before a merge or provide a clean output schema. Determine which name is visible to statements within the step. Renaming a field does not reconcile differing meanings or units, so semantic mapping still needs explicit logic.
With set work.source(rename=(old_id=customer_id));, subsequent DATA step statements use customer_id. With a RENAME option on the output data set, the old name remains the working name inside the step and the new name appears in the saved schema. Choosing the wrong boundary can cause uninitialized-variable messages or missing results because the code references a name that is not available there.
28. How does SET concatenate data sets?
A SET statement listing multiple compatible data sets reads them sequentially into one output stream. Variables absent from a source receive missing values as appropriate, but conflicting types or unsuitable lengths require correction. Define attributes before reading and inspect the resulting schema. Concatenation stacks observations; it does not match rows by a shared identifier.
For monthly extracts, set work.january work.february; reads all January observations and then all February observations. It does not pair January customer 7 with February customer 7. Before stacking, align identifier types, units and character lengths so both sources obey the same schema. A source indicator can be useful for reconciliation, especially when explaining why a field is missing for one month's observations.
29. What does PROC SORT do?
PROC SORT orders observations according to BY variables and specified direction, optionally producing a separate output data set. Sorting supports BY-group processing and deterministic selection when complete tie-breakers are supplied. It can consume significant temporary storage. Do not assume that sorting by a nonunique key defines which tied row should survive a later deduplication step.
To select the latest record per customer, sort by the customer key and a descending timestamp, plus a defined tie-breaker if timestamps can match. Sorting only by customer groups the records but does not express what โlatestโ means. Using OUT= can preserve the source table while creating the ordered intermediate result, and count checks help distinguish sorting from any separately requested duplicate removal.
30. What are FIRST.variable and LAST.variable?
During BY-group processing, SAS creates temporary indicators identifying the beginning and end of groups. They are useful for group totals and selecting boundary observations. Their behavior reflects the full BY-variable hierarchy, and they are not automatically stored in the output. Input must satisfy the applicable ordering or grouping requirements for the chosen operation and engine.
With by region customer;, starting a new region can also set FIRST.customer, even if the customer text happens to equal the final customer text of the preceding region. The flags identify groups in the declared hierarchy, not merely adjacent equality in one isolated column. For a group total, initialize at FIRST.customer, accumulate each amount, and emit the result at LAST.customer using input ordered for that hierarchy.
31. What is the purpose of RETAIN?
RETAIN prevents specified DATA step variables from being reset to missing at the usual iteration boundary and can supply initial values. It enables state carried across observations. It does not automatically reset state at a new BY group; do that explicitly when required. Some variables are already retained by their defining statements, so understand the source of retention before adding more.
The fixture initializes total once and then uses the SUM function to add each amount while ignoring missing arguments. Its expected totals are 10, 10 and 15. RETAIN alone does not decide how missing arithmetic behaves: replacing sum(total, amount) with ordinary addition would produce a missing total when the missing amount is encountered. A grouped version also needs an explicit reset at the group boundary.
SAS example
32. How does a sum statement differ from ordinary addition?
A sum statement such as total + amount retains its accumulator, initializes it to zero unless otherwise specified and treats a missing expression as adding zero. An assignment such as total = total + amount does not provide those same behaviors. For per-group totals, reset the accumulator at FIRST.group to prevent values from leaking across groups.
For amounts 10, missing and 5, a sum statement total + amount; produces 10, 10 and 15 because its retained accumulator ignores the missing contribution. With an ordinary assignment, the initial missing total and later missing operands must be handled explicitly. This is a language construct, not merely shorter spelling for addition, so changing between the two forms can change both initialization and missing-value behavior.
33. What is a SAS array?
A DATA step array groups variables under a name so code can reference them with an index. It is often a programming convenience over existing variables rather than a separate table or a general dynamic collection. Specify dimensions and types consistently. Temporary arrays hold values for processing without automatically creating corresponding output variables and have retention characteristics worth checking.
For columns score1-score3, array scores[3] score1-score3; lets a loop inspect scores[i] without repeating the same expression three times. Updating an element changes the corresponding variable; the array is not a separate copied collection. Iterating to dim(scores) keeps the loop aligned with the declared dimension, but indexes must still stay within bounds. A temporary array is useful for working state that should not appear as output columns.
34. How do iterative DO loops work?
An iterative DO loop executes a block over a sequence of index values, supporting repeated calculations or array processing. The loop's bounds and increment determine termination. OUTPUT inside the loop can create multiple observations per input row. Test zero-iteration and boundary cases, and avoid assuming that the loop index ends at the last in-range value after completion.
In do i=1 to 3;, statements in the body see 1, 2 and 3; after normal completion the index has advanced beyond the upper bound. If each iteration performs output;, one input row can generate three output observations. If the loop executes zero times, no such explicit output occurs, so boundary conditions affect not only calculations but also the number of rows written.
35. How do DO WHILE and DO UNTIL differ?
DO WHILE checks its condition before the loop body, so it may execute zero times. DO UNTIL checks after the body, so the body executes at least once. Choose according to the required first execution and update the controlling state reliably. Missing or incorrectly changed conditions can create unexpectedly long or nonterminating loops.
If a balance is already sufficient, a DO WHILE loop that adds installments while the balance is too low should do nothing. A DO UNTIL version adds an installment before checking, which may be inappropriate for that requirement. Conversely, a process that must attempt an operation once before assessing its result can fit DO UNTIL. State the intended first execution rather than choosing the form by habit.
36. What does OUTPUT change in a DATA step?
An explicit OUTPUT writes the current PDV values at that point. Once explicit output is used, the step's ordinary automatic end-of-iteration output is suppressed. This allows multiple output rows or selective output but can also accidentally omit records. When several output data sets exist, specify destinations deliberately and reconcile counts for every branch.
If a step assigns status='before'; output; and then changes status to 'after' without another OUTPUT, the saved observation contains โbefore.โ Writing the PDV captures its values at that moment. An explicit OUTPUT anywhere in the step means the programmer controls output, so a branch that never reaches it may produce no row even if execution continues to the end of the iteration.
37. What does DELETE do in a DATA step?
DELETE stops the current iteration without writing the ordinary output observation, then proceeds to the next iteration. It does not undo an observation already written by an earlier explicit OUTPUT. Nor does it reverse retained-state updates already performed. Place it with awareness of statement order rather than treating it as a transactional rollback for the row.
In a step that first executes output; and later finds amount < 0 and executes delete;, the already-written row remains. To reject invalid observations, validate before writing them and account separately for rejected input. If the program updates a retained running total before DELETE, that total also remains changed unless the code explicitly compensates or moves validation earlier.
38. How do PROC PRINT and PROC CONTENTS differ?
PROC PRINT displays observations, whereas PROC CONTENTS describes a data set's structure and attributes. Use PRINT to inspect sample values and CONTENTS to investigate types, lengths, formats and ordering metadata. A small printout cannot prove that all rows satisfy a rule. Combine inspection with summaries and explicit validation when checking a production transformation.
If a date appears as a large number in PRINT, CONTENTS can reveal whether a date format is absent. If a supposed date is character, attaching a numeric date format is not the solution; parsing is required. For a wide or sensitive table, restrict the displayed variables and observations deliberately, then use aggregate checks to test the whole population rather than dumping every record into a report.
39. What are PROC MEANS and PROC FREQ commonly used for?
PROC MEANS produces descriptive numeric statistics, while PROC FREQ examines counts and cross-tabulations of categorical values. Their treatment of missing values and grouping depends on options. Specify the statistic, denominator and missing-data policy you intend. A report that runs successfully can still answer the wrong question if categories or excluded observations are misunderstood.
For an amount column, MEANS can compare nonmissing count, missing count, minimum and maximum to expose unexpected gaps or extreme values. For a status column, FREQ can reveal spelling variants and unexpected categories. Decide whether missing status values should appear as a reported category and be included in the denominator; otherwise a percentage can look plausible while silently describing only the nonmissing subset.
40. How does a macro variable differ from a DATA step variable?
A macro variable holds text used by the macro processor to generate or substitute code; a DATA step variable holds a value while observations are processed. Their scope and timing differ. Replacing a token with &name does not make it a row-level calculation. Use macros for controlled code generation and ordinary variables for data transformations.
If %let threshold=100; precedes a DATA step, the macro processor can substitute &threshold into if amount > &threshold; before rows are processed. That text is a shared code parameter, while amount changes for each observation. A value created by CALL SYMPUTX during execution is not available to an earlier macro reference merely because the statements appear in the same step.
SAS Interview Questions and Answers for Intermediate
These answers work through transformations and the timing of evaluation. Small examples expose the difference between reading a value, formatting it, retaining it and generating code that will execute later.
41. How would you import a CSV file reproducibly?
I would treat the CSV layout as a data contract: define column types, character lengths, informats, delimiter, quoting and encoding before reading it. PROC IMPORT is useful for exploration, but schema inference can change when a later file contains different values or longer text.
For example, a customer identifier 00127 belongs in a character variable even if every sampled identifier contains digits. A production DATA step can declare its length explicitly and read amounts with the agreed numeric informat. Validate headers, required fields and row counts against the source, and capture rejected records with a reason.
Test embedded commas, escaped quotes, empty fields, long strings and malformed numbers. A successful step does not prove a faithful import: truncation, invalid conversions or records read across line boundaries can change meaning without an obvious application failure.
42. What do DSD, DLM and TRUNCOVER control during input?
DLM= specifies the delimiter. DSD changes delimiter-sensitive list input so quoted delimiters can remain inside a value and consecutive delimiters represent missing fields; it also makes comma the default delimiter when another is not specified. TRUNCOVER prevents a short input record from borrowing data from the next record to satisfy an INPUT statement.
For a CSV record such as 42,"North, East",,7, the comma inside quotes belongs to the second field and the consecutive commas identify an empty third field. Splitting blindly on every comma would create the wrong number of fields.
These options address different problems and do not replace an explicit schema. Their interaction with list, formatted and column input matters. Include short-record fixtures and confirm whether the desired result is a missing value, a partial field or a rejected record.
43. When should you use the INPUT and PUT functions?
Use INPUT to interpret text with an informat and produce the corresponding value; use PUT to apply a format and produce character text. The destination variable receives a value of that type. Neither function changes the declared type of an existing variable in place.
Here the comma informat interprets the grouping separator and decimal point, while the format creates a display string:
SAS example
The numeric amount is 1250.5; display_amount is character text and may contain leading padding. Keep calculation variables numeric and format them for presentation. For identifiers, avoid numeric conversion when leading zeros are meaningful. Invalid source text needs an explicit error or quarantine policy rather than silently treating the resulting missing value as a real measurement.
44. Why is implicit type conversion risky?
Implicit conversion lets SAS reconcile character and numeric expressions automatically, but the chosen conversion may not match the source contract. It can produce missing values, log notes or unexpected representations. An identifier that looks numeric is still an identifier if its leading zeros or exact text matter.
For example, comparing character customer code '00127' with numeric 127 can hide a type mismatch. Converting it to a number loses the original representation, and values containing letters can produce invalid-conversion messages. I would preserve the raw field and create a separately named converted value only when numeric interpretation is intended.
Use an explicit informat or format, inspect conversion failures and reconcile their counts. Do not suppress notes merely to clean the log: first determine which inputs are invalid and how the downstream calculation should treat them.
45. How do CAT, CATS, CATT and CATX differ?
The CAT family differs in how it treats blanks. CAT concatenates values without trimming, CATT removes trailing blanks, CATS removes leading and trailing blanks, and CATX also inserts a chosen separator between nonblank items.
For building a display label, catx(' / ', region, team) is convenient because an empty team does not leave a dangling separator. For constructing a fixed-layout record, automatic trimming may destroy required spacing, so preserving the original fields can be the correct choice.
Declare the destination length before assignment instead of relying on a function's default result length. A short target can truncate an otherwise correct concatenation. Also distinguish an empty component from a meaningful blank-containing identifier; string cleanup must follow business semantics rather than being applied indiscriminately.
46. How do SCAN and SUBSTR solve different string tasks?
SCAN selects a token according to delimiters; SUBSTR selects characters by position and length. Use SCAN when the structure is delimiter-based and SUBSTR when the contract defines a fixed layout. Choosing by convenience can make a parser work on one sample and fail when field lengths vary.
For AA-204-NORTH, scan(code, 2, '-') identifies token 204 without depending on the first token's width. A fixed-position extract is more appropriate for a file whose first two positions are always a record type. Empty or quoted tokens require explicit SCAN modifier choices.
Check malformed inputs before assuming the requested token or range exists. In multibyte encodings, byte positions and character positions can differ; use the appropriate character-aware SAS functions and test representative non-ASCII input when that distinction matters.
47. How do INDEX and FIND differ in string searching?
INDEX finds a substring using a straightforward search. FIND adds controls such as case-insensitive matching, trimming behavior and a starting position. Both return a position, with zero indicating no match, so test the returned value rather than assuming every input contains the marker.
A case-insensitive search for error can be useful when log text contains Error or ERROR. A positional result tells where the sequence begins, but not whether it is a complete word: searching for cat also finds it in category.
If the requirement concerns tokens, use a word-oriented operation or validate boundaries. Specify case and whitespace rules explicitly, especially for identifiers. Otherwise a broad search can classify unrelated values as matches or miss valid values that differ only in presentation.
48. What does the LAG function actually return?
LAG returns a value from a queue attached to that particular function occurrence. The queue advances when the function executes, not automatically for every input observation. Calling LAG conditionally therefore means the previous qualifying execution, which may not be the previous row.
Suppose values are 10, 20 and 30, but lag(value) executes only when the value exceeds 15. Its results on the qualifying rows are missing and 20: 10 never entered that queue. Separate occurrences of LAG also have separate queues, even if they receive the same variable.
For an ordinary previous-row calculation, execute LAG consistently and then decide whether to use its result. At a BY-group boundary, suppress the prior group's value explicitly. Assigning missing to the result variable does not reset the underlying queue.
49. How can you compute a previous value within each BY group?
Sort by the group key and the intended within-group order, execute LAG for every observation, and suppress its result on the first observation of each group. This keeps queue advancement consistent while preventing the last value from the preceding group from appearing as a valid predecessor.
The sequence variable defines which customer event came first:
SAS example
Expected previous_amount values are missing, 10 and missing. The final missing value is deliberate: customer B has no preceding event within B. If sequence values tie, add a deterministic tie-breaker. A retained previous-value variable is an alternative when state rules are more complex, but calculate or output with the old value before updating it to the current amount.
50. How would you calculate a running total within each customer?
Sort by customer and a deterministic transaction sequence, reset the accumulator on FIRST.customer, then add the current amount. A sum statement retains its accumulator and treats a missing expression as no increment, so that behavior must agree with the reporting rule.
The central logic is if first.customer then total=0; followed by total + amount;. For customer A with amounts 10, missing and 5, totals become 10, 10 and 15 under that policy. Customer B starts from zero rather than inheriting A's total.
A running total describes a sequence, so ordering by an incomplete date key can make intermediate balances unstable. A final total from aggregation may still be correct even when the running balances are wrong. If a missing amount means unknown rather than zero activity, track that condition separately instead of presenting an apparently complete balance.
51. How does a DATA step match-merge differ from a SQL join?
A DATA step match-merge combines observations within ordered BY groups using DATA step processing rules. It does not create all combinations when both inputs repeat a key. A SQL equality join does create those matching combinations, so the two operations can produce different cardinalities from the same inputs.
For one key with two rows on the left and three on the right, a straightforward match-merge emits three observations, whereas a SQL join emits six combinations. During the merge, values from an exhausted input can remain available for the rest of that BY group. Shared non-key variable names introduce additional overwrite behavior.
First establish the intended grain and whether each side's key is unique. If the requirement is every matching pair, use a join with that explicit meaning. If it is a match-merge, sort or index correctly, rename conflicting variables and validate duplicate-key groups rather than assuming the operation is a relational join.
52. What does the IN= data set option tell you during a merge?
The IN= data set option creates a temporary indicator associated with an input. In a match-merge, it is commonly used to distinguish keys represented in both inputs from keys represented on only one side. The indicator is not automatically written to the output data set.
For merge left(in=in_left) right(in=in_right); by id;, if in_left and in_right; selects groups represented in both sources. With repeated keys, an indicator can remain true while that source participates in the BY group, even after its last physical row for that group has been read.
Therefore, do not interpret each true value as proof that a fresh source observation was consumed on that iteration. If auditing requires row-level lineage, retain source row identifiers explicitly. Check indicator behavior with an unequal duplicate-count fixture before using it for reconciliation.
53. How would you produce unmatched records from two sources?
I would first define whether unmatched means a key absent from the other source or a particular row without a one-to-one counterpart. Those are different questions when keys repeat. For absent keys, an anti-join or sorted match-merge with indicators expresses the requirement directly.
With merge indicators, if in_left and not in_right; selects left-only keys. If a left key occurs three times and exists once on the right, those are not left-only keys; a requirement to pair and leave two rows unmatched needs a separate, explicit matching rule.
Preserve source identifiers and reconcile counts of matched, left-only and right-only groups. Decide what missing keys mean before matching. Conventional SAS comparisons can equate missing values, unlike typical database NULL equality, so missing-key records may need to be separated before the operation.
54. Why should duplicate keys be checked before merging?
Duplicate-key checks establish the relationship between the inputs before a join or merge. One-to-one, one-to-many and many-to-many relationships imply different output counts and different risks. I would profile key frequencies on both sides and inspect repeated groups before combining measures.
For example, joining two invoice rows to three status-history rows on customer alone can produce six rows and multiply invoice amounts. The correct fix may be joining on invoice ID, selecting the relevant status as of a date, or aggregating at an agreed grainโnot removing whichever duplicates happen to appear first.
Record expected uniqueness as a validation rule when it is part of the contract. If duplicates are legitimate, document the intended combinations and reconcile totals at a stable grain. Blind deduplication can conceal an upstream defect or discard distinct valid events.
55. What does NODUPKEY remove in PROC SORT?
NODUPKEY retains one observation for each distinct combination of BY values. It is key-based deduplication, not a rule that identifies the most trustworthy or recent record. Columns outside the BY list can differ substantially among discarded observations.
If two rows share customer ID but have different addresses, sorting with NODUPKEY on customer ID removes one address without deciding which is current. Retention can depend on ordering and sort options, so a business requirement such as latest timestamp should be implemented with an explicit ranking rule.
Preserve removed observations with an appropriate audit output such as DUPOUT= when required, and reconcile retained plus rejected counts. Avoid adding every column to the BY list merely to stop removals: that changes the definition of a duplicate instead of resolving the original key-quality problem.
56. How would you select the latest record per identifier?
Define latest using a business timestamp and deterministic tie-breakers, sort by identifier and that ordering, then select the appropriate FIRST. or LAST. observation. For ascending timestamp order, if last.id; selects the final record for each identifier; descending order pairs naturally with FIRST.
A tie-breaker such as a revision number makes the result reproducible when two records share a timestamp. Missing timestamps need an explicit rule rather than reliance on where SAS sorts missing values. If exact ties remain, determine whether they are interchangeable duplicates or require an exception report.
Latest arrival is not necessarily latest effective business state. A correction received today may apply to last month's record. For effective-dated data, specify the requested as-of date and validity interval before selecting a row. Test those cases as well as the simple increasing-timestamp example.
57. How do WHERE and HAVING differ in PROC SQL?
WHERE filters eligible source rows before grouping; HAVING filters grouped or aggregate-related results. For example, WHERE can restrict sales to a reporting period, while HAVING can retain customers whose sum of those sales exceeds a threshold.
The query pattern where sale_date >= '01JAN2026'd group by customer having sum(amount) > 1000 asks for high-spending customers within that period. Moving the amount threshold into WHERE would instead exclude individual small sales before calculating the total, answering a different question.
Also inspect selected columns. Conventional PROC SQL can remerge summaries with detail rows when nongrouped detail values are requested, so GROUP BY does not by itself guarantee one output row per customer. Confirm the intended grain and review any remerge note.
58. How do COUNT(*) and COUNT(column) differ?
COUNT(*) counts result rows, whereas COUNT(column) counts nonmissing values of that expression in PROC SQL. The choice defines the denominator: a missing measurement still belongs to the row population but does not count as an observed measurement.
This fixture contains three rows and two nonmissing numeric values:
SAS example
The expected results are 3 and 2. After a left join, counting a right-side nonmissing identifier can distinguish matched rows from preserved unmatched rows, provided that identifier is reliably populated. Check join cardinality first: duplicated matches can inflate both counts. Neither form automatically counts distinct business entities; use the appropriate key and distinctness rule when that is the requirement.
59. Why can PROC SQL remerge summary statistics?
PROC SQL can calculate a group statistic and remerge it with detail rows when the SELECT list also requests detail values not fully represented by the grouping. The result can therefore retain detail grain while repeating a summary value across those rows.
For example, selecting department, employee and average salary while grouping only by department can attach the department average to each employee. That may be intentional for comparison with individual salaries, but it is wrong if the expected output is a department summary table with one row per department.
Read the remerge note and inspect output counts. To enforce summary grain, select only the grouping variables and required aggregates. When attaching a summary is intentional, a separate summary table and explicit join can make the grain easier to review. NOREMERGE can reject queries that would otherwise perform this behavior.
60. How can a LEFT JOIN accidentally behave like an INNER JOIN?
A left join preserves unmatched left rows by supplying missing right-side values. A subsequent WHERE predicate that those missing values cannot satisfy removes the preserved rows, making the result behave like an inner join for that condition.
For example, where right.status='ACTIVE' excludes an unmatched customer's blank status. Placing that condition in ON instead means only active rows are eligible matches, while customers without an active match remain in the result. These are different reporting requirements, so predicate placement should follow the intended population.
Test the actual predicate under SAS missing-value rules. A numeric condition such as right.score < 10 can admit numeric missing values in conventional SAS, so it does not behave like the usual NULL-filtering example. Add explicit missing checks when the requirement calls for observed values.
61. How does CALCULATED work in PROC SQL?
CALCULATED refers to a SELECT expression alias within supported contexts of the same PROC SQL query. It lets a later expression use a calculated value without repeating the original formula, but it is a SAS SQL feature rather than portable database SQL syntax.
For example, select price*quantity as gross, calculated gross*0.10 as discount makes the dependency visible. Choose aliases that do not conflict ambiguously with source-column names, and check the context where alias reuse is supported rather than assuming arbitrary forward references work.
For database-backed work, consider whether the expression can be pushed down and whether native SQL supports an equivalent construct. A subquery that defines gross and an outer query that uses it can make evaluation scope clearer and improve portability. Avoid treating alias reuse as evidence that an expensive function is evaluated only once.
62. How do UNION and UNION ALL differ?
UNION combines compatible query results and eliminates duplicate result rows; UNION ALL retains them. Duplicate elimination compares the selected row values, not an unstated business key, and typically adds processing work.
If two source systems each contain a valid payment of 100 for the same date, selecting only date and amount with UNION can collapse two distinct payments into one. Include source and transaction identifiers when those distinctions matter, or use UNION ALL when every event should survive.
Check column alignment and data types: corresponding positions must represent the same concepts unless an explicit supported correspondence option is intended. Do not choose UNION merely as a cleanup shortcut. Define whether duplicate output rows are redundant copies or separate events before deciding which operation preserves the data's meaning.
63. When would you use EXISTS instead of a join?
Use EXISTS when the question is whether at least one related row exists, without needing its columns. It expresses membership while avoiding multiplication of the left rows solely because several right-side matches qualify.
For example, selecting customers where an unpaid invoice exists should produce each qualifying customer row once, even if the customer has five unpaid invoices. A direct join can produce five copies unless the query deliberately aggregates or deduplicates. EXISTS makes the intended test clearer.
It does not automatically remove duplicates already present in the left table, and it is not universally faster than a join. Execution depends on the engine, indexing and optimizer. Correlate on the full intended key and apply an explicit missing-key policy; an incomplete correlation can make nearly every left row qualify.
64. How do SQL pass-through and SAS-side SQL differ?
Explicit SQL pass-through sends SQL to a database through its connection, using that database's dialect and execution rules. Ordinary PROC SQL can read SAS tables and engine-backed tables and may push supported operations to the database, but the visible SAS syntax alone does not establish where every operation runs.
For a large database table, filtering and aggregation near the data can avoid transferring millions of rows to SAS. However, a SAS-only function may prevent pushdown or require a different database expression. Validate date types, character handling and missing/NULL semantics when translating the query.
Inspect appropriate engine diagnostics or database plans to establish execution location and row movement. A faster result is not automatically equivalent if rounding, collation or missing comparisons changed. Keep credentials in managed connection configuration rather than embedding them in generated programs or debug logs.
65. How does PROC TRANSPOSE reshape data?
PROC TRANSPOSE reshapes values between rows and columns according to the selected variables, BY groups and ID values. Define the input and output grain first. For long-to-wide data, an ID value often becomes an output column name within each BY group.
Suppose customer A has rows for measures HEIGHT and WEIGHT. With customer as the BY group and measure as the ID, the desired output is one customer row with separate measure columns. Two HEIGHT rows for A create an ambiguity: the procedure needs a deliberate rule, such as prior aggregation, rather than an accidental winner.
Validate ID uniqueness and generated names, including the effects of formatting or naming restrictions. New source categories can create unexpected columns and change the downstream schema. Reconcile the reshaped values to the original data, and do not assume transposition itself aggregates duplicate measurements.
66. How do CLASS and BY processing differ in summary procedures?
BY processing handles groups in the sequence established by suitable ordering or indexing. CLASS variables let supporting summary procedures classify observations without the same presort requirement. They also expose options for combinations of classification levels, so CLASS is not merely a shorter way to write BY.
For a sales summary by region and product, CLASS can request cell-level summaries and subtotal combinations without separately processing a sorted stream for each group. BY processing is useful when data is already organized appropriately or when separate group processing is required.
Check missing-class handling and the output combinations generated by the procedure. A subtotal row with a missing-looking class value may represent an aggregate level rather than missing source data. Inspect _TYPE_ and relevant options before interpreting those rows. Choose the approach based on output grain, memory and ordering constraints.
67. What does the NWAY option do in PROC SUMMARY?
NWAY requests the highest-order combination of the CLASS variables in PROC SUMMARY. With region and product as CLASS variables, it retains their complete combinations instead of also emitting the grand total and lower-order region-only or product-only summaries.
That is useful when the downstream table requires one row per regionโproduct cell. Without checking summary levels, joining the output back to detail data can accidentally mix subtotals with cell totals or inflate measures.
NWAY does not decide which statistics to calculate, nor does it automatically include missing classification values. Specify the desired output statistics and missing-value policy separately. Inspect _TYPE_, _FREQ_ and the class columns to verify grain; an analysis variable's nonmissing count can differ from the number of observations contributing to a class cell.
68. How can ODS OUTPUT help build reproducible reports?
ODS OUTPUT captures a named procedure output object as a SAS data set. It separates statistical results from their rendered appearance, making downstream validation and report formatting reproducible without scraping HTML, PDF or printed text.
Use ODS TRACE while developing to identify the relevant object name, then capture that object with an ODS OUTPUT statement around the procedure. For a model report, this could capture parameter estimates for checks or a standardized presentation rather than copying numbers from a displayed table.
Verify the resulting columns and rows for the procedure options and release in use: different analyses can expose different objects or schemas. Close or reset ODS OUTPUT deliberately after the intended procedure. Also validate that the analysis actually produced the object; a failed model should not leave a later report using an old result table.
69. How would you compare two SAS data sets?
Use PROC COMPARE with an explicit alignment rule and a numeric comparison criterion appropriate to the calculation. Compare structure as well as values: variable type, length and format differences can explain downstream behavior even when displayed values look similar.
If two data sets contain the same customer rows in different orders, observation-by-observation comparison can report false mismatches. Sort and identify a suitable unique key when comparing corresponding business records. Duplicate keys require resolution or an additional identifier, not merely an ID statement that hides the ambiguity.
Choose numeric tolerances from the computation's expected precision and the business consequence of error. Do not widen them until the test passes. Review missing-value differences and row counts separately, retain the comparison results, and make the pipeline fail when differences exceed the documented acceptance rule.
70. What does PROC APPEND trade off against rebuilding with SET?
PROC APPEND adds observations to an existing base table without necessarily rewriting its existing observations, which can be more efficient than repeatedly rebuilding the full table with SET. The gain is especially relevant when a large historical table receives small batches.
The base table's attributes govern compatibility. A longer incoming character value can be truncated to a shorter base length, and FORCE can allow differences that lose information. FORCE is therefore not a substitute for comparing types, lengths and required variables before the append.
Stage and validate each incoming batch, reconcile added rows and establish a recovery or restart strategy. Re-running an append after an uncertain failure can duplicate records unless the process has a batch identity or other idempotency control. Choose rebuilding when transformation or schema changes require it rather than treating append as universally safer.
71. How do local and global macro variable scopes differ?
Global macro variables are available broadly in the SAS session; local macro variables belong to a macro invocation's symbol table. Declare scratch variables with %LOCAL so an assignment does not accidentally change an existing global variable with the same name.
For example, a reporting macro using a loop counter called i should not alter a caller's global i. Macro parameters are local to the invocation, while resolution can also find variables in enclosing scopes. Nested calls therefore need deliberate naming and scope rather than relying on a session that happens to be clean.
Use globals for intentional shared configuration, not hidden return channels for every calculation. Explicit interfaces make repeated calls easier to test and prevent one program's stale values from affecting another. Scope and macro-resolution timing are separate concerns; a correctly scoped value can still be read too early.
72. How do positional and keyword macro parameters differ?
Positional macro parameters receive values by argument order. Keyword parameters are named and can have defaults, making larger interfaces easier to read and extend. For example, %report(data=work.sales, region=WEST) communicates the role of each argument more clearly than an unexplained sequence of values.
Positional parameters suit a small, stable interface; adding another position can otherwise break existing callers or make omitted values difficult to interpret. Keyword defaults should express a deliberate behavior, such as a default output library, rather than silently compensate for missing required inputs.
Validate parameters before generating SAS code. A data-set name, integer limit and free-text title require different checks and quoting. Commas or macro triggers inside a value can affect argument parsing, so macro quoting may be needed, but quoting alone does not establish that the eventual generated code is valid.
73. Why do %EVAL and %SYSEVALF behave differently?
%EVAL evaluates integer-oriented macro expressions; %SYSEVALF supports floating-point evaluation and optional conversion behavior. Macro variables remain text: the evaluator interprets their contents for an operation rather than turning them into persistent typed numeric variables.
For example, %eval(5/2) uses integer arithmetic, while %sysevalf(5/2) can represent 2.5. Choosing the wrong evaluator for a threshold containing a fraction can cause an error or change the intended condition. Boolean conversion options are useful when a floating-point result must control macro logic.
Keep macro evaluation separate from DATA step evaluation. A macro condition decides what code to generate; it does not evaluate each observation's variable values. Validate numeric parameter text before evaluation and consider missing or blank values explicitly rather than assuming every supplied string is a number.
74. What is macro quoting used for?
Macro quoting masks characters or words that the macro processor would otherwise treat as syntax. It is needed when data contains commas, ampersands, percent signs or mnemonic operators that should remain literal during a particular stage of macro processing.
The choice depends on timing. Compilation-oriented functions such as %STR and execution-oriented quoting functions address different situations, and variants differ in which triggers they mask. For a text parameter containing a comma, the goal may be to keep that comma inside one argument instead of splitting the macro call.
Macro quoting is not ordinary SAS string quoting and is not a general sanitizer. After macro processing, the text may enter a SAS statement, SQL expression or identifier context with its own rules. Validate that destination separately and unquote only when required; indiscriminate unquoting can reactivate syntax that was meant to remain data.
75. How does %SUPERQ help handle macro variable contents?
%SUPERQ retrieves a macro variable's contents while masking macro-special characters and preventing embedded references from being resolved further. Pass the variable's name, not an already-expanded &name value, because expansion first can trigger the behavior you intended to avoid.
If a variable contains a label such as Research & Development, %superq(label) can preserve the ampersand as data during macro processing instead of treating it as the start of another reference. It is particularly useful when inspecting or passing text whose contents are not known in advance.
The protection applies to macro interpretation, not every later execution context. If the value will be inserted into a quoted SAS string, generated SQL or a file path, apply that context's validation and escaping rules too. Literal preservation does not make arbitrary generated code safe or meaningful.
76. How does CALL SYMPUTX transfer data to the macro facility?
CALL SYMPUTX transfers a DATA step value into a macro variable, trims surrounding blanks and supports explicit symbol-table selection. It executes when the DATA step runs. A conventional macro reference in that same step may already have resolved before the new value exists.
Place the consuming reference after a clear step boundary:
SAS example
Here RUN completes the DATA step before the %PUT reference is processed. The example deliberately selects global scope and removes only its own fixture variable afterward. In a reusable macro, choose scope to match the interface instead of creating globals accidentally. When a value must be retrieved during execution, consider SYMGET rather than assuming an earlier macro expansion will update itself.
77. How does SYMGET differ from a macro variable reference?
SYMGET retrieves a macro variable's value during DATA step execution and returns character text. An ordinary &name reference is generally resolved by the macro processor before the corresponding DATA step statements execute. That timing difference matters when the macro value is created or changed while the step runs.
For instance, after a CALL SYMPUTX executes, a subsequent text=symget('threshold'); can retrieve the current symbol-table value. A previously resolved &threshold has already become source text and does not refresh just because the macro variable changed.
Use an explicit INPUT conversion if the retrieved text participates in numeric calculations. Also make the dependency visible: a DATA step that reads session-level state can behave differently after another program changes it. Pass stable configuration before the step when dynamic execution-time lookup is not actually needed.
78. What timing issue can CALL EXECUTE introduce?
CALL EXECUTE resolves generated text and queues SAS statements for execution after the current DATA step boundary. A macro invocation in that text can execute earlier, while the DATA step is still running, so macro processing and the generated SAS statements do not necessarily share one execution time.
A failure occurs when the invoked macro expects a macro variable to have been created by a generated DATA step: the macro reference can resolve before that queued step executes. Merely placing the statements earlier in the generated text does not guarantee the expected macro timing.
Make generation and execution separate phases where practical, and inspect the emitted program. Carefully chosen macro quoting can defer macro resolution, but it must be justified for the actual expression and quoting context. Test with values that are absent initially so stale session variables cannot make an ordering defect appear to work.
79. How do MPRINT, MLOGIC and SYMBOLGEN assist debugging?
MPRINT shows SAS statements generated by macros, MLOGIC traces macro execution decisions, and SYMBOLGEN shows macro-variable resolution. They answer different debugging questions and are most useful when enabled selectively around the failing invocation.
If a generated WHERE condition is wrong, MPRINT reveals the submitted statement. If the expected branch never generated it, MLOGIC helps trace the condition. If the condition contains an unexpected value, SYMBOLGEN helps locate the resolution or scope problem. Read the ordinary SAS errors and notes as well; generated code still has normal compilation and execution rules.
Debug traces can expose parameter values, credentials or sensitive filters. Use synthetic inputs where possible, restrict log access and restore the intended options afterward. More logging is not a correction; reduce the failure to a small call and verify the repaired generated program.
80. How would you validate a macro parameter before use?
Validate each macro parameter according to its role before emitting code. A required table name needs existence and naming checks; a count needs numeric and range checks; an option may need membership in an allowed set. Free text needs a destination-specific quoting strategy rather than identifier validation.
For a macro that summarizes an amount column, confirm that the input table exists, the column exists and is numeric, and the output target is acceptable before starting the transformation. Reject an invalid option with a clear message instead of quietly choosing a default that changes the requested analysis.
Keep validation failures from leaving misleading partial output. Macro quoting protects parsing at the macro layer, but does not establish that a table is authorized, a variable has the required meaning or an SQL fragment is safe. Prefer a narrow parameter interface over accepting arbitrary executable text.
SAS Interview Questions and Answers for Experienced
These scenarios require candidates to diagnose an incorrect or slow pipeline before changing it. Strong responses separate data contracts, execution environment, access controls and recovery responsibilities.
81. How would you investigate an unexpectedly small output table?
Trace counts through each filtering, join and output stage, starting with the earliest divergence. Check subsetting conditions, explicit OUTPUT behavior, missing values, BY-group logic and source availability. Review the log for rejected input or errors. Reproduce the issue with a small fixture that preserves the failing pattern instead of repeatedly rerunning the entire production job.
For example, if an input has ten thousand observations but a merge produces only six hundred, I would compare the input counts, matched-key counts and the effect of the final subsetting condition separately. An if a and b; condition deliberately keeps only matched BY groups. An explicit output; inside one branch can instead omit observations unintentionally because it replaces automatic output. The first stage where the count changes unexpectedly is a better starting point than the final report.
82. How can a program finish without errors yet produce wrong results?
Many semantic mistakes are valid SAS syntax: joining at the wrong grain, treating missing as a low number, truncating identifiers or retaining state across groups. Execution success therefore establishes only that the program ran under its checks. Validate invariants, reconcile totals and test known cases. Separate technical completion from business acceptance in job status reporting.
Suppose two transactions for one customer join to three address records. PROC SQL can legitimately create six rows, and summing the transaction amount afterward multiplies the business measure. There may be no syntax error to flag. I would require tests for the expected transaction grain and reconciled amounts, not simply a check that the output data set exists. The quality result should distinguish 'execution completed' from 'validated for release' so downstream scheduling cannot treat those states as equivalent.
83. How would you design a rerunnable SAS pipeline?
Use stable input snapshots, explicit parameters and deterministic transformation rules, then write outputs through controlled staging and promotion. Record which inputs and code version produced each result. A rerun must not silently duplicate appends or mix old and new partitions. Define cleanup, recovery and publication boundaries so failed attempts can be diagnosed and safely repeated.
For a daily extract, I would identify the business date and source version separately from the attempt's start time. Each attempt writes to its own staging location, checks schema, row counts and key constraints, and only then makes a complete result available to consumers. An append destination also needs a durable batch key or reconciliation step to distinguish a retry from new data. I would not claim that renaming a collection of files makes the whole pipeline transactional; the publication mechanism must fit the actual storage engine.
84. How do you distinguish a checkpoint from a trustworthy restart point?
A checkpoint records progress, but a trustworthy restart point also establishes that its outputs are complete, consistent and tied to known inputs and parameters. Validate manifests, counts or checksums before reuse. If upstream data changed, resuming from an old intermediate table may be invalid. Design restart dependencies explicitly rather than relying on the presence of a file.
A table named stage3 is weak evidence: an interrupted DATA step may have left an incomplete result, or it may belong to yesterday's input. I would pair each reusable stage with a completion record containing its input versions, parameters, code revision and validation outcome. If stage two's contract changes, dependent checkpoints become invalid even when their files remain readable. Testing a restart immediately before and immediately after publication reveals ambiguous states that a successful end-to-end run never exercises.
85. How would you handle schema drift in incoming data?
Compare incoming metadata against a versioned contract, distinguishing compatible additions from breaking type, length or meaning changes. Quarantine or reject unsupported changes and preserve diagnostic evidence. Automatic inference can conceal drift by changing the target schema from one run to another. Update transformations and downstream consumers deliberately, with fixtures for both old and new contracts.
A customer identifier changing from numeric to character is not necessarily cosmetic: leading zeros or new alphabetic prefixes can carry identity. I would inspect metadata with PROC CONTENTS or dictionary tables before transforming the file, then compare types and lengths with the accepted contract. Additive columns can be ignored or adopted deliberately; incompatible changes should produce a clear rejected-batch result. Repeatedly using inferred imports can allow a quiet type change to propagate into joins before anyone notices it.
86. How would you diagnose character truncation across a pipeline?
Identify the earliest stage where a value becomes shorter, inspecting variable lengths, input informats, concatenation targets, merge attributes and encoding changes. Compare raw input to intermediate values rather than only the final report. Fix the responsible schema or expression and assess affected historical data. Increasing the final column length cannot restore characters already lost upstream.
I would compare the variable's declared length at each boundary, not only the longest displayed value. If an identifier first becomes a five-byte field, assigning it later into a twenty-byte field preserves the already truncated five bytes. In a DATA step, the order in which attributes are established matters, so an explicit LENGTH declaration may need to precede SET or the first assignment. I would also distinguish stored truncation from a narrow display format before deciding which historical outputs require rebuilding.
87. Why can encoding migration change character-processing results?
Different encodings can represent the same visible text with different byte sequences and widths. Fixed-length fields and byte-oriented functions may therefore truncate or split characters after migration. Test multilingual fixtures and review transcoding messages and character-aware alternatives. Successful opening of a data set does not establish that every identifier, sort order or string transformation remained semantically unchanged.
A field that held ten single-byte characters may require more than ten bytes in UTF-8. I would check the session encoding, data-set encoding and declared byte lengths before rewriting the data, then test accented and multibyte text rather than ASCII alone. SAS's CVP engine can expand character lengths while reading supported SAS data files for migration. Character-aware K functions may be needed where the intended operation counts characters. Increasing storage lengths alone does not repair a byte-oriented substring that splits a multibyte character.
88. How would you define reliable missing-data handling?
Document missingness by variable and business context, distinguishing unknown, not applicable, not collected and invalid when those meanings matter. Decide how each category affects filters, joins, summaries and reporting denominators. In Base SAS, numeric missing values sort below ordinary numbers, so a simple upper-bound comparison can include them. Test explicit missing cases rather than assuming database NULL behavior.
For a rule 'amount must be below 100,' if amount < 100; also includes numeric missing values in conventional Base SAS. If missing amounts must be excluded, the condition needs an explicit nonmissing test. I would separately decide whether missing keys may match, whether missing amounts contribute zero or remain unknown, and which observations belong in a rate's denominator. Collapsing every missing category into zero can make both reconciliation and later analysis misleading, even if the final table looks complete.
SAS example: exclude numeric missing values explicitly. The fixture keeps only 25; missing is not treated as a small valid amount.
89. What can cause many-to-many joins to inflate totals?
Repeated keys on both sides create combinations of matching observations, multiplying measures before aggregation. Compare key cardinalities and row counts at the join boundary. Pre-aggregate to a shared grain, add a meaningful join condition or model the relationship explicitly. DISTINCT applied afterward may hide the symptom while still removing legitimate rows or preserving incorrectly multiplied values.
If an account has two invoices and three contacts, joining both tables only by account yields six rows. Summing invoice values then counts each invoice three times. I would first decide whether the result should represent invoices, contacts or account summaries. Pre-aggregating invoices to account grain can be correct for an account report, but discards invoice-level detail. Choosing one contact requires an actual business rule, such as the effective primary contact, rather than an arbitrary duplicate removal that happens to restore the old row count.
90. How would you reconcile a DATA step merge with a database join?
Start with matched-key counts, duplicate distributions and missing-key semantics, then compare results at a defined grain. DATA step many-to-many merging and SQL joins follow different rules, so syntactic translation is insufficient. Build fixtures containing duplicates and nonmatches on both sides. Also inspect shared non-key variable names, which can overwrite values during a merge.
For one BY key with two rows in A and three in B, a conventional DATA step match-merge produces three observations, while an SQL equijoin produces six combinations. The merge pairs rows sequentially and carries values from an exhausted side within the group; it is not a Cartesian combination. I would build this tiny fixture before translating production code. Even when keys are unique, shared non-key names deserve explicit renaming so the result does not depend accidentally on which input supplies the later value.
91. How would you choose between sorting and indexed access?
Compare selectivity, repeated access patterns, table size, update frequency and engine support. An index can help selective retrieval but adds storage and update cost; a sequential scan may be better when most rows are needed. Sorting can enable efficient grouped processing. Measure the actual plan and I/O rather than assuming an available index will always be used or beneficial.
If a report retrieves one customer from a large Base SAS data set, an index might avoid reading most pages. If it retrieves nearly every customer, scattered indexed access may cost more than a sequential read. A sort has an upfront cost but can serve several later BY-group steps if the ordering remains valid. I would compare the complete sequence, including index maintenance or repeated sorts, and inspect the engine's diagnostics. Remote database access has its own optimizer and should not inherit Base SAS index assumptions automatically.
92. When is an in-memory hash object useful in a DATA step?
A hash object can support lookups, joins or deduplication without sorting the large driving table, provided the lookup state fits available memory. Define keys, data fields and duplicate-key behavior carefully. Check return codes and handle unmatched rows explicitly. It is an implementation choice with memory and initialization costs, not a universal replacement for PROC SQL or sorted merges.
A common use is attaching a small product dimension to a large transaction stream. Load the dimension once, define the key and returned attributes, and perform a lookup for each transaction. This can avoid sorting the transaction table, but the dimension's actual in-memory footprint must fit. I would define what duplicate product keys mean before loading it: a single-value lookup is not automatically a one-to-many join. If unmatched products are possible, the output should carry an explicit match flag instead of silently using a missing or previous attribute.
93. How can stale values affect hash lookup output?
If a lookup fails and target data variables still contain values from a previous successful lookup, output can incorrectly appear matched. Initialize or clear the relevant variables according to the program's control flow and branch on the lookup return code. Test success followed by failure, not only isolated cases. Retention and PDV behavior make this sequencing especially important.
The important fixture is two lookups in sequence: a known key followed by an unknown one. Within the same DATA step iteration, the second failed FIND does not supply new data values, so a previous successful value must not be treated as its result. Clearing the lookup fields before searching and interpreting rc=0 as success makes the intended behavior explicit. This is especially valuable when variables are retained by other parts of the program or several lookups occur before one output operation.
SAS example: clear returned data before a lookup can fail. The first row has rate 0.2; the second has a missing rate and a nonzero return code. Both lookups occur in one iteration to make stale-state risk visible.
94. How would you assess compression for a SAS data set?
Compare storage savings with CPU cost, row patterns, access behavior and engine support. Repeated character content can compress differently from dense numeric data. Benchmark both reads and writes on representative workloads and check available disk space during conversion. Compression reduces physical size in some cases; it does not change the logical row count or replace a retention policy.
I would create a separate compressed copy of representative data and compare its physical size and the time to perform the actual scans or updates. A character-heavy table with repeated padding can behave differently from an already dense numeric table. The test also needs space for both copies during conversion. Smaller storage can improve an I/O-bound scan while additional compression work can hurt a CPU-bound workload, so file-size reduction by itself is not enough to justify changing the production setting.
95. How would you reduce unnecessary I/O in a SAS job?
Read only needed variables and observations where semantics permit, eliminate redundant sorts and intermediate rewrites, and keep work near its data source when supported. Measure which steps dominate I/O before tuning. Early filtering can alter stateful calculations or BY-group boundaries, so performance changes must preserve results. Retain intermediates that serve a justified recovery or audit purpose.
Dropping unused wide character columns at the input boundary can reduce the data carried through sorts and intermediate writes. Moving a filter earlier requires more care: filtering transactions before calculating a running balance changes the balance, whereas filtering the displayed result afterward may preserve the intended calculation. I would first inspect step-level resource measurements and the sizes of intermediate tables, then remove the largest avoidable movement. A retained intermediate used for validated restart may be worth its I/O cost rather than merely redundant.
96. How can you verify that SQL work was pushed to a database?
Use the relevant SAS/ACCESS diagnostic options and database execution evidence for the deployed engine, then inspect the generated or submitted SQL. A remote libref alone does not prove every expression ran remotely. Unsupported functions or cross-source operations can pull data into SAS. Validate semantics, credentials and data movement as well as elapsed time.
For example, a selective predicate on a remote table should ideally be applied before millions of rows cross the connection. I would use the SAS/ACCESS engine's supported tracing facilities, with secrets redacted, and corroborate them with the database query history or plan. A SAS function in the predicate may or may not translate for that engine. Explicit pass-through makes the submitted SQL more visible, but also makes the program depend directly on database syntax and semantics; it is not a substitute for validating the result.
97. Why can pushing a query to a database change results?
The database may use different NULL rules, collation, case sensitivity, numeric precision, date functions or implicit conversions than SAS-side processing. A query that is faster can therefore still be wrong for the intended contract. Compare representative edge cases and specify conversions explicitly. Treat execution-location changes as behavior changes requiring validation, not merely infrastructure tuning.
Missing keys provide a useful migration test. Conventional PROC SQL can match SAS missing values with one another, while an ordinary database equality predicate does not make NULL equal NULL. A join moved remotely can therefore lose matches that existed locally. Character comparisons and timestamp conversions can change too. I would define the desired rule rather than automatically reproduce an accidental local behavior, then compare small edge-case fixtures on both execution paths before assessing whether the performance improvement is acceptable.
98. How would you benchmark two SAS implementations fairly?
Use the same input snapshot, parameters and expected result, and capture elapsed time, CPU, I/O and memory where available. Account for caches, startup costs and concurrent load, and repeat enough to understand variability. Verify output equivalence before comparing speed. A faster implementation that changes missing-value handling or duplicate cardinality is not an optimization of the same computation.
A sort-merge and a hash lookup should be compared using the same duplicate-key contract and unmatched-row policy. Otherwise a faster result may simply contain fewer observations. I would capture a baseline, use PROC COMPARE with appropriate keys and numeric criteria, and reconcile counts independently. For timing, I would record whether caches were warm and include lookup-table initialization when the job pays that cost each run. Resource measurements such as those available through FULLSTIMER should be interpreted for the host and deployment rather than treated as universal fields.
99. How does CAS execution differ from a conventional Compute DATA step?
CAS can distribute DATA step processing across multiple threads and workers, whereas conventional Compute DATA step reasoning commonly assumes a single sequential stream. Only supported constructs and data arrangements execute in CAS. Thread-local state, ordering and final-row logic need review. Confirm the actual execution location and relevant Viya release instead of inferring it from the presence of a CAS library.
A row-by-row transformation of independent observations is easier to distribute than a calculation whose next result depends on the previous observation. I would inspect the execution log to confirm where the step actually ran, because a program that accesses CAS data can still execute on Compute. Migration testing should deliberately change partitioning and input size. Agreement on a tiny sample does not show that retained state or a 'last row' calculation has a meaningful global interpretation when workers process separate portions of the table.
100. Why can retained accumulators give unexpected results in CAS?
In multithreaded CAS processing, each thread can maintain its own state, so a retained accumulator may produce partial totals rather than one global total. Use supported aggregation or an explicit strategy for combining partial results. Forcing single-thread execution can change performance and is not always the right solution. Validate grouping and output cardinality under the intended deployment.
Suppose a DATA step adds each sale to a retained total and outputs only at the end of its local input. Distributed execution can yield multiple partial totals, one for each processing thread, rather than one company-wide result. A supported aggregation can combine the data directly, or a designed two-stage calculation can combine partial results. I would verify both the total and the number of output observations. For averages, combining partial averages without their counts is wrong when threads process different numbers of observations.
101. What ordering assumptions should you revisit when moving to CAS?
Do not assume the original physical row order will define a stable global sequence in distributed processing. Operations involving previous rows, FIRST./LAST. selection or running totals need an explicit supported ordering and grouping strategy. Add meaningful tie-breakers and test across partitions. A result that happens to match in a small single-worker environment may not generalize.
For a 'latest event per account' calculation, the ordering contract should include event time and a deterministic tie-breaker such as an event identifier. Arrival or storage order is not a reliable substitute. I would confirm the deployed CAS operation supports the required grouping and within-group ordering, then test accounts spanning the original input layout. If two records have the same timestamp but different statuses, the program must have a documented selection rule; changing worker count must not silently choose a different winner.
102. How do session-scoped and promoted CAS tables differ?
Session-scoped CAS tables are associated with a session, while promoted tables can have broader global visibility within the applicable caslib and permissions. Promotion concerns table scope, not necessarily durable storage across restarts. Saving data to persistent backing storage is a separate concern. Verify ownership, access and lifecycle behavior for the actual Viya deployment before relying on continued availability.
If an analyst needs another session to query an in-memory result, promotion may be appropriate subject to caslib access controls. If the requirement is to recover after a server restart, I would separately arrange persistence to a supported data source and a reload process. Those are different acceptance tests: cross-session visibility versus recovery from saved data. I would also establish who can replace the global table and whether a consumer could see an old session-scoped table with the same name during testing.
103. How would you decide whether a workload belongs in CAS?
Assess data volume, parallelizable work, supported operations, data-loading costs and expected reuse. Small jobs or sequential stateful transformations may gain little from distribution, while repeated large analytical workloads may benefit. Include memory capacity, concurrency and governance requirements. Benchmark end-to-end elapsed time, including movement and setup, instead of comparing only the fastest isolated processing step.
Consider a large table reused for several analytical procedures: loading it once and performing repeated supported work in CAS may amortize the movement cost. A small daily lookup that is loaded, processed once and discarded may spend more time on setup than it saves. I would measure the whole job, including source access, loading and saving, under expected concurrency. Keeping a sequential preprocessing step on Compute while moving suitable analytics to CAS can be a better design than forcing every line into distributed execution.
104. How would you migrate a SAS 9 workflow to Viya safely?
Inventory dependencies on engines, procedures, macros, filesystem paths, scheduling, authentication and sequential execution. Establish equivalent outputs on representative fixtures and full-scale data before changing execution location or architecture. Some code can remain on Compute while selected work moves to CAS. Migrate in controlled stages with rollback and operational monitoring rather than assuming identical syntax implies identical behavior.
I would first reproduce the existing accepted outputs on Compute, then move selected operations to CAS or replace components in separate changes. That makes a discrepancy easier to attribute to platform setup, encoding, data access or distributed semantics. Filesystem paths, format catalogs and externally invoked programs are common hidden dependencies. A migration fixture should include duplicate keys, multilingual identifiers and empty input, not just typical numeric data. Cutover also needs equivalent scheduling, permissions, monitoring and restart behavior; matching one report does not establish operational readiness.
105. How would you secure credentials used by SAS jobs?
Use the deployment's approved credential or authentication mechanisms and grant only the access required by each job. Avoid embedding secrets in programs, macro parameters or logs. Review debugging options that reveal resolved values and protect connection metadata and output locations. Credential management must also cover rotation, service ownership and failure recovery without exposing private values during diagnosis.
A job that retrieves data successfully can still leak its credential if macro tracing prints the resolved connection statement. I would review MPRINT, SYMBOLGEN, explicit %PUT statements and error paths before enabling detailed diagnostics. The approved mechanism depends on SAS 9 versus the specific Viya deployment, so I would use the configured authentication service rather than invent a file-based secret store. A rotation test should confirm the job continues using its intended service identity and does not fall back to an analyst's personal access.
106. How can dynamically generated SAS code create a security risk?
Untrusted text inserted into statements can change the generated program rather than remain a data value. Restrict permitted identifiers and operations, validate parameters and use the appropriate quoting for each destination. Macro quoting alone is not an authorization boundary. Log safe execution context and review generation templates without exposing secrets or executing unreviewed input as code.
A report parameter intended to select a column should be checked against an allowed set of column names; arbitrary text should not be concatenated into a generated statement. A data value needs quoting appropriate to its eventual SAS or SQL context. %SUPERQ can prevent further macro resolution while handling a macro variable's contents, but it does not decide whether an operation is allowed or make every later use safe. I would separate identifier parameters from literal values and reject unexpected syntax before code generation.
107. How would you test a reusable macro library?
Test each macro's parameter contract, generated code, outputs, errors and side effects on session options or global variables. Include nested calls, repeated calls, missing inputs and special characters. Use small deterministic fixtures with expected tables and capture logs. A macro that produces valid text for one example may still fail through scope collisions or timing in a larger pipeline.
For a macro that summarizes a table, I would run it twice with different inputs in one session and then from a clean session. That exposes undeclared global state, leaked temporary table names and reliance on options left by previous code. Inputs should include spaces or macro-sensitive characters where the contract permits them, plus a missing required parameter. Tests should check that failure leaves no misleading final output and that temporary changes are restored. Generated-code inspection complements output tests because a valid example can conceal unsafe parameter interpolation.
108. What makes a useful SAS regression test?
A useful regression test detects a meaningful behavior change using explicit expected values, schemas and row counts. It should cover boundary cases such as missing keys, duplicate observations, date transitions and zero-row inputs. Control ordering and numeric tolerances so failures are interpretable. A test that merely reruns the same transformation without an independent expectation provides little assurance.
For a merge macro, one compact regression fixture should include a matching key, a key on only one side and a repeated key. Expected rows should be written independently rather than generated by another invocation of the same macro. PROC COMPARE can compare values and attributes, but the test must choose whether rows align by position or by a valid unique ID. I would explicitly test an unexpected extra row as well: comparing only the overlapping values misses part of the contract.
109. How would you validate floating-point results?
Choose absolute and relative tolerances based on the scale, algorithm and business requirement, then compare against independently derived expected values where feasible. Binary floating-point arithmetic can introduce small representation differences. Do not equate formatted display equality with stored equality. Financial or regulated calculations may require additional rules for rounding, units and reproducibility that must be explicitly documented.
I would compare stored values with a criterion that reflects the calculation's scale, using explicit METHOD= and CRITERION= choices when using PROC COMPARE. A relative-only test can be unstable near zero, while a loose absolute tolerance can hide meaningful differences in small quantities. I would test zero, small and large expected values separately. Missing values also need an explicit policy, and a display format that rounds two different values to the same text must not become the equality test.
110. How would you preserve data lineage in a SAS pipeline?
Record input identifiers, extraction times or versions, code revision, parameters, transformation stages and output identifiers in durable run metadata. Include reference-data versions and rejected-record counts. Lineage should allow a result to be explained and reproduced without relying on an analyst's session history. Keep sensitive row contents separate from broadly accessible operational metadata.
For a result such as a monthly customer summary, the lineage record should identify the source extract, customer-reference version and business-month parameter as well as the program revision. If a corrected reference table changes a category, those details explain why a rerun differs even when the transformation code is unchanged. A stable run ID should connect intermediate validation results, rejected-record counts and final outputs. I would keep row-level exception data in a restricted artifact and link to it from operational metadata rather than copy confidential records into every log.
111. How would you handle late-arriving or corrected source records?
Define the business key, effective time, arrival time and correction policy before processing. Use an idempotent strategy to replace, supersede or adjust affected results, and determine which downstream partitions need recomputation. Blind append can duplicate corrected events. Preserve enough history to explain revisions while ensuring reports consistently apply the chosen as-of or latest-truth definition.
An event can have an effective date in October but arrive in November. A latest-truth report may need October recomputed, whereas an as-of report may need to preserve what was known at the earlier cutoff. I would retain both timestamps and identify corrections by a stable business key or version. The update should replace or supersede the prior contribution exactly once. Repeating the same corrected batch must not add the adjustment again, and downstream summaries need a dependency rule that identifies which periods become stale.
112. How would you make a large append process recoverable?
Stage and validate incoming data, assign a batch identifier and maintain a durable record of committed batches. Ensure retry logic can detect whether an earlier attempt already applied the batch. Separate validation from final promotion where the storage engine permits. A successful partial append followed by a failed later step must not lead to duplicate rows on restart.
A batch manifest alone is insufficient if the process can crash after appending rows but before marking the batch committed. On restart, 'not committed' would then cause a second append. I would close that ambiguity with an engine-supported transaction when available, or use stable batch identifiers plus reconciliation or replaceable staged partitions. The recovery test should interrupt the job on both sides of the publication boundary. PROC APPEND finishing is one step; it does not make an entire multi-step workflow atomic.
113. How should a SAS pipeline handle zero-row inputs?
Treat zero rows as an explicit scenario with a defined outcome: valid empty result, warning or failure depending on the contract. Preserve the expected schema and avoid relying on DATA step code that executes only after a SET successfully reads an observation. Macros and summaries can otherwise reuse stale values. Test empty sources independently from missing or inaccessible sources.
A particularly subtle case is assigning a macro variable inside a DATA step after SET: if there are no observations, that assignment is never reached. A value left from an earlier run can then drive later code. I would initialize control values independently and decide whether an empty but correctly structured table is acceptable. A missing source is a different condition and should not be converted silently to a valid empty result. The downstream report should distinguish 'zero qualifying events' from 'no data delivered.'
SAS example: create a deliberate empty table with a stable schema. The variable attributes compile, while STOP prevents an observation from being written. This is an empty fixture, not a substitute for validating a missing production input.
114. How would you investigate a program that hangs or runs indefinitely?
Inspect the currently executing step, resource usage, locks and log progress, then distinguish slow I/O from a nonterminating loop or uncontrolled code generation. DATA steps using unusual input control, such as POINT=, need explicit termination. Reproduce with bounded inputs and add diagnostic limits safely. Do not repeatedly launch additional copies of a potentially blocked production job.
If CPU is busy and the log stops inside a DATA step that uses SET ... POINT=, I would check its loop bounds and explicit stop condition; random-access reading does not provide ordinary sequential end-of-file termination. If CPU is low while the process waits, locks or remote I/O may be more likely. I would preserve the current step and sanitized diagnostics before cancelling according to operational policy. Launching duplicate attempts can create more contention or competing output writers without solving the original problem.
115. How can session state undermine reproducibility?
Options, librefs, formats, macro variables, titles and ODS destinations can persist and affect later code. A program that works only after another program ran has hidden dependencies. Initialize required state explicitly and restore temporary changes when designing reusable components. Test from a clean session so missing setup is detected instead of inherited accidentally from development activity.
A program may rely on a custom format already present in FMTSEARCH, a libref assigned interactively or a global macro variable defined by a previous submission. Those dependencies disappear in a scheduled clean session. I would declare the required libraries, options and parameters at the entry point, then save and restore temporary changes inside reusable components where practical. Tests should also run two different jobs in the same session to expose contamination in the opposite direction, such as a leftover title or ODS destination affecting the second job.
116. How would you manage user-defined formats as dependencies?
Version the format definitions with the pipeline and ensure required catalogs or generated definitions are available in each execution environment. Formats can influence display and, in some procedures, grouping or classification behavior. Missing-format handling should be deliberate. A successful table transfer without the matching format definitions may produce misleading reports or inconsistent category interpretation.
Suppose a format maps several diagnosis codes into one reporting category. Changing that mapping can change grouped results without changing the underlying stored codes. I would version the format source or control data set and record the version used for each run. Moving only the data file is insufficient if the receiving environment lacks the catalog or searches a different one first. Using an option that suppresses missing-format errors can permit execution, but it is not evidence that the intended categorization survived the migration.
117. What should an operational quality gate check before releasing a result?
Check required inputs, schema compatibility, key constraints, row-count and total reconciliations, rejected records, relevant log conditions and expected output availability. Use thresholds justified by the business process and distinguish warnings from failures. Release only after the entire intended batch is complete. A single clean procedure log is insufficient evidence for a multi-step pipeline's correctness.
For an invoice pipeline, a gate might require one row per invoice, no unexplained increase in rejected rows and reconciliation of the accepted amount with source totals after documented exclusions. A broad percentage threshold should not hide a missing high-value account. I would make critical constraints hard failures and reserve warnings for deviations that have a defined review path. The gate should reference the complete run and its expected partitions, so an early successful procedure cannot release a partially completed month's result.
118. How would you schedule dependent SAS jobs reliably?
Represent dependencies explicitly and start a downstream job only after its required upstream outputs pass validation. Record run identifiers, timeouts, retries and ownership in the approved scheduler. A file appearing on disk does not necessarily mean its writer finished. Prevent overlapping runs from replacing shared outputs, and separate business dates from wall-clock execution dates.
The scheduler should consume an explicit successful validation state or completion manifest, not merely wait for a table name to appear. An upstream DATA step may create its output before it has finished writing every observation. I would assign distinct run locations or enforce a concurrency lock where overlapping attempts could replace the same outputs. Retries should reuse the intended business date and input snapshot rather than recalculate them from today's clock, particularly for a delayed month-end run or a daylight-saving transition.
119. How would you design diagnostic logging without exposing sensitive data?
Record step identifiers, counts, timing, sanitized parameters and error categories rather than full confidential records. Review PUT statements and macro-debugging options for accidental disclosure. Restrict detailed diagnostic artifacts and apply retention rules. Logs should support investigation and reconciliation while avoiding credentials, personal information and unnecessary row-level payloads in broadly accessible monitoring systems.
When investigating a join failure, counts of matched and unmatched keys may be enough for the shared log; the actual unmatched identifiers can go to a restricted exception table. PUT _ALL_; is convenient in development but can expose every PDV value, and macro tracing can reveal resolved credentials. I would use a synthetic reproduction for detailed code tracing whenever possible. Operational logs need enough context to correlate a failure with its run and stage, plus controlled access and retention for the rarer cases requiring row-level evidence.
120. How would you review a senior SAS developer's solution?
Ask the developer to explain row grain, PDV behavior, missing values, duplicate handling, macro timing and execution location for a concrete workflow. Introduce a schema change, an empty input and a restart after failure. Strong answers preserve semantics and provide testable recovery strategies. Evaluate maintainability and evidence alongside speed, rather than rewarding dense code or undocumented cleverness.
I would use one small scenario rather than ask for isolated definitions: merge a transaction table with reference data, calculate a summary and rerun after a failed publication. Then add duplicate reference keys and an empty transaction source. A strong candidate explains why the row count changes, how retained or macro state behaves and what establishes a safe retry. If they propose moving the work to CAS or a database, they should identify the semantic assumptions that change and the fixture that would detect a regression.
SAS Documentation and Further Reading
Use these primary SAS references to check language semantics and product-specific execution details. The legacy language references document established Base SAS behavior; the Viya material addresses CAS differences. Verify release-specific restrictions and engine behavior against the actual deployed environment.
Similar Posts
