A00-232
Free A00-232 practice questions
10 questions from our A00-232 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.
Question 1 of 10, Accessing Data Using SQL
A programmer needs one macro variable that holds every distinct value of Region from WORK.SALES, separated by commas, for use in a later IN list. Which statement creates it?
- Aselect distinct Region, ',' into :reglist from work.sales group by Region;
- Bselect distinct Region into :reglist1-:reglist99 separated by ',' from work.sales;
- Cselect distinct quote(trim(Region)) into :reglist from work.sales separated by ',';
- Dselect distinct quote(trim(Region)) into :reglist separated by ',' from work.sales; Correct answer
Answer and explanation: The INTO clause comes directly after the SELECT list, and SEPARATED BY concatenates the values from all rows into one macro variable; QUOTE(TRIM()) makes each value a quoted string suitable for an IN list. SEPARATED BY cannot follow the FROM clause. Combining a numbered range with SEPARATED BY is not how a single delimited list is built. Selecting a comma constant into one macro variable stores only values from the first row.
Question 2 of 10, Accessing Data Using SQL
Table SALES has 12 rows in 3 regions. A programmer runs: proc sql; select Region, Amount, sum(Amount) as RegTotal from work.sales group by Region; quit; What does the query produce?
- A12 rows where RegTotal holds the running cumulative sum of Amount within each region in table order
- B3 rows, one per region, with Amount taken from the last row read in each region and the regional total beside it
- C12 rows, each Amount beside its region's total, with a log note that summary statistics were remerged Correct answer
- DAn error, because every column in the SELECT clause that is not summarized must be listed in the GROUP BY clause
Answer and explanation: When the SELECT list mixes a summary function with a non-grouped detail column, PROC SQL remerges the summary value back onto every detail row and writes a NOTE about remerging, so all 12 rows come back. It does not collapse to one row per group because Amount is not summarized. Unlike ANSI databases, SAS does not treat this as an error unless NOREMERGE is set. SQL summary functions never compute running totals.
Question 3 of 10, Accessing Data Using SQL
WORK.PRICES holds new prices by ProdID. All rows of WORK.PROD with a matching ProdID must receive the new price, and the others must keep their price. Which UPDATE statement does this?
- Aupdate work.prod as p set Price = (select n.Price from work.prices as n where n.ProdID = p.ProdID) where ProdID in (select ProdID from work.prices); Correct answer
- Bupdate work.prod as p set Price = (select n.Price from work.prices as n where n.ProdID = p.ProdID) where Price is not missing and Price > 0;
- Cupdate work.prod as p set Price = n.Price from work.prices as n where n.ProdID = p.ProdID;
- Dupdate work.prod as p, work.prices as n set p.Price = n.Price where p.ProdID = n.ProdID and p.ProdID in (select ProdID from work.prices);
Answer and explanation: The correlated subquery fetches each product's new price, and the WHERE clause limits the update to products that have one. Filtering only on the current Price still updates products that have no match, and those are set to missing because the subquery returns no row. PROC SQL UPDATE accepts only one table, so a multi-table UPDATE is invalid. The UPDATE ... FROM form used by some databases is not supported.
Question 4 of 10, Accessing Data Using SQL
Customer names in CRM are stored in mixed case and those in BILLING in uppercase. A join must match them regardless of case and surrounding blanks. Which ON clause works?
- Aon upcase(strip(c.Name)) = upcase(strip(b.Name)) Correct answer
- Bon soundex(c.Name) = soundex(b.Name)
- Con trim(c.Name) eqt trim(b.Name)
- Don c.Name = b.Name and c.Name is not missing
Answer and explanation: PROC SQL join conditions can use expressions, so normalizing both sides with UPCASE and STRIP matches names regardless of case and blanks. A plain equality is case-sensitive. Comparing SOUNDEX codes would also match names that merely sound alike, such as Smith and Smyth. EQT compares after truncating to the shorter length, which can match different names that share a prefix and is still case-sensitive.
Question 5 of 10, Macro Processing
A DATA step creates a macro variable and tries to use it in the same step: data _null_; set work.totals; call symputx('grand', Total); put "Total is &grand"; run; The macro variable GRAND did not exist before this step. What happens?
- AThe step fails to compile, because CALL SYMPUTX cannot be used in a DATA _NULL_ step
- BThe PUT statement writes a blank value, because SYMPUTX stores numbers as null text
- CThe PUT statement writes the value of Total, because CALL SYMPUTX runs before the PUT
- DA warning says GRAND is unresolved, because &grand is resolved before the step executes Correct answer
Answer and explanation: Macro references in the step's code are resolved by the word scanner when the step is compiled, before any DATA step statement executes. CALL SYMPUTX only creates the macro variable at execution time, so &grand is unresolved during compilation and a WARNING is written. The order of statements does not matter for this timing. DATA _NULL_ steps can use CALL SYMPUTX, and SYMPUTX converts numbers to text with leading and trailing blanks removed.
Question 6 of 10, Macro Processing
A DATA step runs: data _null_; set work.params; call execute('%runrpt(' || strip(Region) || ')'); run; The macro RUNRPT uses %LET and %IF logic that depends on values created by DATA steps inside the macro. Why is this risky, and what fixes it?
- AThe macro logic runs while the DATA step executes; %NRSTR around the call delays it to the step end Correct answer
- BThe macro runs only after every step has finished; wrapping the call in %STR forces it to run immediately
- CThe generated code runs in a new SAS session; adding the SYSPARM= option keeps the same session
- DCALL EXECUTE cannot pass parameters to a macro; using CALL SYMPUTX for each parameter is required
Answer and explanation: When CALL EXECUTE receives a macro invocation, the macro executes right away, so macro statements such as %LET and %IF run while the calling DATA step is still executing, while the generated SAS steps are queued until after it ends; any macro logic that depends on those steps sees stale values. Writing call execute('%nrstr(%runrpt(' || strip(Region) || '))') delays the macro call until the queued code runs. %STR does not mask the percent sign. CALL EXECUTE can pass arguments, and it runs in the same session.
Question 7 of 10, Macro Processing
A macro takes a WHERE condition as a positional parameter: %macro sel(cond); proc print data=work.cars; where &cond; run; %mend sel; %sel(Make='Audi' and MSRP > 40000) The call fails with an error saying that the keyword parameter MAKE was not defined. What fixes it?
- ACall it without parentheses: %sel Make='Audi' and MSRP > 40000;
- BMask it in the call: %sel(%str(Make='Audi' and MSRP > 40000)) Correct answer
- CWrap the reference inside the macro: where %eval(&cond);
- DQuote the value in the call: %sel("Make='Audi' and MSRP > 40000")
Answer and explanation: In a macro call, text of the form name=value is read as a keyword parameter assignment, so Make= is treated as an undefined keyword; masking the value with %STR passes the whole condition to the positional parameter. Double quotation marks would become part of the value, giving a WHERE statement with only a string constant. Without parentheses, no parameter values are passed. %EVAL would try to evaluate the condition as integer arithmetic and fail on the text comparison.
Question 8 of 10, Advanced Techniques
A DATA step needs a lookup list of five tax rates that should not be written to the output data set and should keep their values across iterations. Which statement is the best fit?
- Aarray rate{5} _temporary_ (0.10 0.12 0.22 0.24 0.32); Correct answer
- Bretain rate1-rate5 0.10 0.12 0.22 0.24 0.32; drop rate:;
- Carray rate{5} $4 _temporary_ ('0.10' '0.12' '0.22' '0.24' '0.32');
- Darray rate{5} rate1-rate5 (0.10 0.12 0.22 0.24 0.32);
Answer and explanation: A _TEMPORARY_ array holds its elements in memory only, is never written to the output data set and is automatically retained, which fits a lookup table. Named elements RATE1-RATE5 would be written to the output unless dropped. A character temporary array stores the rates as text, which is unsuitable for arithmetic. RETAIN plus DROP works but needs extra statements and still creates PDV variables, so it is not the best fit.
Question 9 of 10, Advanced Techniques
A programmer uses a DOW loop to sum Amount by Customer in a data set sorted by Customer: data work.tot; do until (last.Customer); set work.trans; by Customer; Total = sum(Total, Amount); end; keep Customer Total; run; Why is Total correct for each customer without a RETAIN statement or a reset to 0?
- AKEEP limits the PDV, so Total is automatically retained across iterations of the step
- BEach iteration handles one customer, and Total is reset to missing at its start Correct answer
- CThe BY statement resets computed variables to 0 whenever a new BY group begins
- DThe SET statement inside a DO loop automatically retains all computed variables
Answer and explanation: In a DOW loop one DATA step iteration processes a whole BY group, so the automatic reset of computed variables to missing at the top of each iteration starts each customer fresh, and SUM ignores the initial missing. Values are not retained by SET; within the loop they simply persist because the step has not returned to the top. BY does not reset variables. KEEP controls output variables, not retention.
Question 10 of 10, Advanced Techniques
WORK.CUST is indexed on CustID. A DATA step reads transactions and must retrieve each matching customer row directly by key: set work.cust key=CustID / unique; if _iorc_ ne 0 then do; _error_ = 0; call missing(Name); end; What is the purpose of the IF statement?
- AIt stops the step when the index on CustID cannot be found in WORK.CUST
- BIt checks that CustID is a unique index before the first SET runs in the step
- CIt retries the keyed read when another user has CUST locked for update
- DIt handles keys missing from CUST, clearing _ERROR_ and carried-over values Correct answer
Answer and explanation: With KEY=, a failed lookup sets _IORC_ to a nonzero value and sets _ERROR_ to 1, which would dump the PDV to the log; resetting _ERROR_ and clearing the CUST variables handles the miss cleanly. UNIQUE tells SAS to start the search from the top of the index for each lookup, not to validate the index. A missing index causes a compile-time error. The code does not retry locked reads.