A00-232 SAS Certified Professional: Advanced Programming Using SAS 9.4 Practice Questions
Prepare for A00-232 with more than an answer.
- Exam fee
- $180 USD
- Level
- Professional
- Valid for
- Does not expire
Domains covered on the exam 3
- Accessing Data Using SQL35%
- Macro Processing35%
- Advanced Techniques30%
- 1
The following macro is submitted:
%macro conditional_logic(category);
%if &category = A %then %do;
%put 'Category A processing.';
%end;
%else %if &category = B %then %do;
%put 'Category B processing.';
%end;
%else %do;
%put 'Other category processing.';
%end;
%mend;%conditional_logic(A)
What will be written to the SAS log?
Show answer details
Correct answer: A
The macro is called with the value 'A' for the
categoryparameter. The macro processor evaluates the first%ifcondition,&category = A, which is true. Therefore, the code inside the corresponding%then %do;block is executed, which is the%put 'Category A processing.';statement. The%elsebranches are skipped. - 2
You need to construct a single query that returns a list of all unique customer IDs present in either the
Q1_Salestable or theQ2_Salestable. WhichPROC SQLset operator is designed for this purpose?flowchart LR subgraph Q1_Sales A[ID: 1] B[ID: 2] end subgraph Q2_Sales C[ID: 2] D[ID: 3] end subgraph Result E[ID: 1] F[ID: 2] G[ID: 3] end Q1_Sales -->|UNION| Result Q2_Sales -->|UNION| ResultShow answer details
Correct answer: D
The
UNIONset operator combines the results of two queries and, by default, returns only the unique rows. This is exactly what is needed to get a distinct list of customer IDs that appear in either of the two sales tables.OUTER UNIONwould include duplicate rows. - 3
When using a hash object in a DATA step, which of the following methods must be called after all keys and data have been defined, but before any find() or add() methods can be used?
Show answer details
Correct answer: C
The
.definedone()method signals that all key and data variable definitions are complete. It must be called after all.definekey()and.definedata()methods and before you can use methods like.find(),.add(), or load data from a dataset. This method finalizes the structure of the hash object in memory. - 4
A SAS programmer needs to use the
TRIMDATA step function within a macro expression to remove trailing blanks from a macro variable's value before a comparison. Which macro function must be used to executeTRIM?Show answer details
Correct answer: C
The
%SYSFUNCmacro function is a bridge that allows you to execute most DATA step functions (and user-written functions) within the macro facility and use their results as text. The correct syntax would be%sysfunc(trim(&myvar)). Without%SYSFUNC, the macro processor would not recognizeTRIMas a valid function. - 5
A programmer is writing a query to identify products that were sold in Q1 but not in Q2. The product IDs are in
work.Q1_Salesandwork.Q2_Sales. WhichPROC SQLquery correctly identifies these products?erDiagram Q1_Sales ||--o{ Products : contains Q2_Sales ||--o{ Products : contains Q1_Sales { int ProductID PK decimal Sales } Q2_Sales { int ProductID PK decimal Sales } Products { int ProductID PK string ProductName }Show answer details
Correct answer: B
The
EXCEPTset operator returns the unique rows from the first query that are not found in the second query. This directly answers the question of which products appear inQ1_Salesbut are absent fromQ2_Sales. - 6
A financial services company needs to generate a report of all customer transactions from the
work.transactionsdataset. For each transaction, the report must include the customer's current city from thework.demographicsdataset. The final report must include all transactions, even if a customer's demographic record is missing. If a customer's city is missing, the report should display 'Unknown'. Which PROC SQL query correctly fulfills this requirement?Show answer details
Correct answer: B
This is the correct solution. A
LEFT JOINis required to ensure all records from thework.transactionstable are included, regardless of a match inwork.demographics. TheCOALESCEfunction is then used to replace any resulting null values in thecitycolumn with the string 'Unknown', meeting all requirements. - 7
A developer is creating a data-driven macro to generate summary reports for a list of products. The product IDs are stored in a dataset
WORK.PRODUCT_LIST. The macro needs to create a series of macro variables, one for each product ID, namedprod1,prod2,prod3, etc. Which code snippet correctly populates these indexed macro variables?Show answer details
Correct answer: B
This is the correct method. In a DATA step, the automatic variable
_N_represents the current iteration number. By concatenating the string 'prod' with the value of_N_, we can dynamically create macro variable names likeprod1,prod2, etc.CALL SYMPUTXis the appropriate routine for assigning values to macro variables from a DATA step. - 8
A programmer needs to combine a very large transaction dataset (
WORK.TRANSACTIONS, 50 million rows) with a small product lookup table (WORK.PRODUCTS, 2,000 rows) to add theProductNameto the transaction data. TheWORK.TRANSACTIONSdataset is not sorted byProductID. What is the most computationally efficient method to perform this lookup, minimizing both CPU time and I/O operations?Show answer details
Correct answer: C
This is the most efficient method. Loading the small lookup table into a hash object in memory avoids the massive I/O overhead of sorting the 50-million-row transaction table. The in-memory hash lookup is extremely fast, making this the best choice for performance when joining a large table to a small one, especially when the large table is unsorted.
- 9
You are debugging a complex SAS macro that dynamically generates SQL code. You suspect that a macro variable is not resolving to the expected value, causing the SQL query to fail. You need to see the exact value of the macro variable as it is being resolved by the macro processor. Which system option is specifically designed for this purpose?
flowchart TD Start[Macro Call] --> MacroProcessor{Macro Processor} MacroProcessor -->|Resolves &myvar| SymbolGenLog[SYMBOLGEN writes: &myvar resolves to 'Value'] MacroProcessor -->|Generates Code| MPrintLog[MPRINT writes: proc sql... where col = 'Value'] SymbolGenLog --> MPrintLog MPrintLog --> ExecutionShow answer details
Correct answer: C
The
SYMBOLGENsystem option is specifically designed to write the results of macro variable resolution to the SAS log. It is the most direct way to see what value a macro variable reference (like&myvar) resolves to during macro execution, making it essential for debugging issues related to incorrect variable values. - 10
A marketing analyst needs to find all customer comments in the
SURVEY.RESPONSEStable that contain either the word 'service' or 'support', but not when these words are part of a larger word (e.g., 'serviced' should not match). The search must be case-insensitive. WhichWHEREclause correctly implements this logic using PRX functions?Show answer details
Correct answer: C
This is the correct solution. The
\bis a regular expression metacharacter for a word boundary. Placing it before and after the grouped terms(service|support)ensures that the match occurs only for the whole words. The/imodifier at the end makes the pattern matching case-insensitive.PRXMATCHreturns true if the pattern is found.
