A00-232 Sample Questions & Answers

Accessing Data Using SQL · Filter grouped data using HAVING clause.

• A

  WHERE

• B

  GROUP BY

• C

  ORDER BY

• D

  HAVING

• E

  FILTER

Macro Processing · Use macro evaluation functions. (%SYSEVALF)

• A

  The log displays: The result is 1

• B

  The log displays: The result is 0

• C

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

• D

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

Advanced Techniques · Create custom functions with the FCMP procedure

• A

  True

• B

  False

Advanced Techniques · Retrieve previous values with the LAG function.

• A

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

• B

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

• C

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

• D

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

Accessing Data Using SQL · Use PROC SQL invocation options (INOBS=, OUTOBS=, NOPRINT, NUMBER)

• A

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

• B

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

• C

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

• D

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

Macro Processing · Use macro quoting functions. (%NRSTR, %STR)

• A

  %QUOTE()

• B

  %BQUOTE()

• C

  %NRSTR()

• D

  %STR()

Advanced Techniques · Process data using 1 and 2 dimensional arrays.

• A

  Use a series of 12 IF-THEN statements to check each month's sales and a long SUM() function with all 12 variables listed.

• B

  Use PROC TRANSPOSE to reshape the data from wide to long, then use a DATA step with BY processing to calculate the sum and check for high values.

• C

  Define a numeric array over the 12 sales variables, then use a DO loop to iterate through the array to calculate the total and check each element's value.

• D

  Write a macro that generates the necessary assignment and IF statements for each of the 12 months.

Macro Processing · Create data-driven programs using SAS Macro Language.

• A

  Create a DATA step that uses a SET statement with RAWSALES.US RAWSALES.CA RAWSALES.DE ... to append the data. Then, create a separate macro for each country to run PROC MEANS and call each macro manually.

• B

  Write a macro that takes the country code as a parameter. Manually create a %DO loop that iterates through a hard-coded list of country codes, calling the macro for each one. Use PROC APPEND inside the loop to build the global table.

• C

  Use PROC SQL to query DICTIONARY.MEMBERS for all datasets in the RAWSALES library. Use the INTO : clause to store the dataset names in a macro variable. Then, create a macro that uses a %DO loop to iterate through the macro variable list, running PROC APPEND and PROC MEANS for each dataset.

• D

  Use a DATA step with CALL EXECUTE. The DATA step reads DICTIONARY.MEMBERS and constructs strings for PROC APPEND and PROC MEANS statements, which are then pushed to the stack for execution. This creates WORK.GLOBALSALES and the summary reports.

Accessing Data Using SQL · Use the CALCULATED keyword.

• A

  It allows you to refer to a calculated column in the SELECT clause of the same query.

• B

  It can only be used in the HAVING clause to filter grouped data.

• C

  It allows you to refer to a column's alias in the WHERE or HAVING clause of the same query.

• D

  It must be used with an aggregate function like SUM or AVG.

Advanced Techniques · Specify a template using the PICTURE statement within the FORMAT Procedure

• A

  picture money_fmt low-high = '9.9M' (prefix='$', mult=0.000001);

• B

  picture money_fmt low-high = '$00.0M' (multiplier=.000001);

• C

  picture money_fmt low-high = '$9.9' (suffix='M', mult=1000000);

• D

  picture money_fmt low-high = '$.M' (datatype=numeric, scale=1000000);