A00-232 sample questions

Question 1

A financial services company needs to generate a report of all customer transactions from the `work.transactions` dataset. For each transaction, the report must include the customer's current city from the `work.demographics` dataset. 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?

Accepted Answer

proc sql; select t.*, coalesce(d.city, 'Unknown') as city from work.transactions as t left join work.demographics as d on t.customer_id = d.customer_id; quit;

Answer

proc sql; select t.*, d.city from work.transactions as t inner join work.demographics as d on t.customer_id = d.customer_id; quit;

Answer

proc sql; select t.*, d.city from work.transactions as t full join work.demographics as d on t.customer_id = d.customer_id; quit;

Answer

proc sql; select t.*, case when d.city is null then 'Unknown' else d.city end as city from work.transactions as t right join work.demographics as d on t.customer_id = d.customer_id; quit;

Question 2

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, named `prod1`, `prod2`, `prod3`, etc. Which code snippet correctly populates these indexed macro variables?

Accepted Answer

data _null_; set work.product_list; call symputx('prod'||_N_, product_id); run;

Answer

proc sql noprint; select product_id into :prod1 - :prod999 from work.product_list; quit;

Answer

%macro create_vars; %do i = 1 %to &sqlobs; %let prod&i = &product_id; %end; %mend;

Answer

data _null_; set work.product_list; call execute('%let prod'||_N_||'='||product_id||';'); run;

Question 3

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 the `ProductName` to the transaction data. The `WORK.TRANSACTIONS` dataset is not sorted by `ProductID`. What is the most computationally efficient method to perform this lookup, minimizing both CPU time and I/O operations?

Accepted Answer

In a DATA step, load the `WORK.PRODUCTS` dataset into a hash object, then iterate through `WORK.TRANSACTIONS` and use the `.FIND()` method to look up the `ProductName`.

Answer

Sort both datasets by `ProductID` using `PROC SORT`, then combine them using a `MERGE` statement in a DATA step.

Answer

Use a `PROC SQL` left join to connect `WORK.TRANSACTIONS` with `WORK.PRODUCTS` on `ProductID`.

Answer

Create a custom format from the `WORK.PRODUCTS` dataset using `PROC FORMAT` with the `CNTLIN=` option, then apply this format to the `ProductID` in a new DATA step.

Question 4

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 --> Execution

Accepted Answer

SYMBOLGEN

Answer

MLOGIC

Answer

MPRINT

Answer

MTRACE

Question 5

A marketing analyst needs to find all customer comments in the `SURVEY.RESPONSES` table 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. Which `WHERE` clause correctly implements this logic using PRX functions?

Accepted Answer

where prxmatch('/\b(service|support)\b/i', comments);

Answer

where prxmatch('/service|support/', comments);

Answer

where findw(comments, 'service',,'i') or findw(comments, 'support',,'i');

Answer

where prxmatch('/^(service|support)$/i', comments);

Question 6

A data analyst is tasked with creating a summary report from a `SALES` table. The report needs to show the total revenue for each product category, but only for categories that have generated more than $1,000,000 in total revenue. Which two clauses must be used in the `PROC SQL` step to achieve this? (Select TWO).

Accepted Answer

GROUP BY

Accepted Answer

HAVING

Answer

WHERE

Answer

ORDER BY

Answer

FILTER

Question 7

A SAS programmer has written the following macro. The intent is to pass a numeric value and have the macro evaluate if it is greater than 100.

%macro check_value(val);
%let result = %eval(&val > 100);
%put The result is &result;
%mend;

The programmer then calls the macro with a non-integer value: %check_value(val=105.5).

What is the outcome of this macro call?

Accepted Answer

An ERROR is written to the log because %EVAL only supports integer arithmetic.

Answer

The log displays: The result is 1

Answer

The log displays: The result is 0

Answer

A WARNING is written to the log, and the expression is evaluated using the integer portion of the number (105).

Question 8

To create a user-defined function that can be used in a DATA step or a WHERE clause, you must use PROC FCMP. True or False?

Accepted Answer

True

Answer

False

Question 9

A research institute is analyzing longitudinal patient data stored in `WORK.VISITS`, sorted by `PatientID` and `VisitDate`. For each visit, the analyst needs to calculate the number of days since that patient's *previous* visit. If it is the patient's first visit, the result should be a missing value. Which code snippet correctly computes this difference?

Accepted Answer

data want; set work.visits; by PatientID; DaysSinceLast = dif(VisitDate); if first.PatientID then DaysSinceLast = .; run;

Answer

data want; set work.visits; by PatientID; retain PrevVisitDate; DaysSinceLast = VisitDate - PrevVisitDate; if last.PatientID then PrevVisitDate = .; PrevVisitDate = VisitDate; run;

Answer

data want; set work.visits; by PatientID; DaysSinceLast = VisitDate - lag(VisitDate); if first.PatientID then call missing(DaysSinceLast); run;

Answer

proc sql; create table want as select *, VisitDate - lag(VisitDate) as DaysSinceLast from work.visits group by PatientID; quit;

Question 10

A programmer needs to query the SAS `DICTIONARY.TABLES` view to get a list of all datasets in the `WORK` library. However, they want to limit the result to the first 5 tables found and prevent the results from printing to the output window. Which `PROC SQL` statement correctly achieves this?

Accepted Answer

proc sql noprint outobs=5; select memname from dictionary.tables where libname = 'WORK'; quit;

Answer

proc sql; select memname from dictionary.tables where libname = 'WORK' and obs <= 5; quit;

Answer

proc sql; select memname from dictionary.tables(obs=5) where libname = 'WORK'; quit;

Answer

proc sql noprint; select top 5 memname from dictionary.tables where libname = 'WORK'; quit;

SAS Certified Professional: Advanced Programming Using SAS 9.4 Free Sample Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Lifetime One

Two

Pro Four

Premium Twelve

A00-232 Sample Questions

Trusted by professionals at