Advanced SAS Interview Questions and Answers Part – 2

11.   How can we use COUNTW function in a macro

Ans: %let cntvar = %sysfunc(countw(&nvar));

There are several useful Base SAS function that are not directly available in Macro, %Sysfunc enables those function to make them work in a macro.

12. %let x=temp;
%let n=3;
%let x3=result;
%let temp3 = result2;
What %put &&x&n; and %put &&&x&n; would return?

Ans: &&x&n : Two ampersands (&&) resolves to one ampersand (&) and scanner continues and then N resolves to 3 and then &x3 resolves to result.
&&&x&n : First two ampersands (&&) resolves to & and then X resolves to temp and then N resolves to 3. In last, &temp3 resolves to result2.

13. How to reference a macro variable in selection criteria

Ans: Use double quotes to reference a macro variable in a selection criteria. Single quotes would not work.

How to reference a macro variable in selection criteria
SAS : Reference Macro Variable

14.  How to debug %IF %THEN statements in a macro code

Ans: MLOGIC option will display how the macro variable resolved each time in the LOG file as TRUE or FALSE for %IF %THEN.

15. Difference between %EVAL and %SYSEVALF functions

Ans: Both %EVAL and %SYSEVALF are used to perform mathematical and logical operation with macro variables. %let last = %eval (4.5+3.2); returns error as %EVAL cannot perform arithmetic calculations with operands that have the floating point values. It is when the %SYSEVALF function comes into picture.
%let last2 = %sysevalf(4.5+3.2);
%put &last2;

16. What would be the value of i after the code below completes

data test;
set temp;
array nvars {3} x1-x3;
do i = 1 to 3;
if nvars{i} > 3 then nvars{i} =.;
end;
run;

Answer is 4. It is because when the first time the loop processes, the value of count is 1; the second time, 2; and the third time, 3. At the beginning of the fourth iteration, the value of count is 4, which is found to be greater than the stop value of 3 so the loop stops. However, the value of i is now 4 and not 3, the last value before it would be greater than 3 as the stop value.

17. How to compare two tables with PROC SQL

Ans: The EXCEPT operator returns rows from the first query that are not part of the second query.
proc sql;
select * from newfile
except
select * from oldfile;
quit;

18. Selecting Random Samples with PROC SQL

The RANUNI and OUTOBS functions can be used for selecting N random samples. The RANUNI function is used to generate random numbers.

proc sql outobs = 10;
create table tt as
select * from sashelp.class
order by ranuni(1234);
quit;

19. How to use NODUPKEY kind of operation with PROC SQL

Ans: In PROC SORT, NODUPKEY option is used to remove duplicates based on a variable. In SQL, we can do it like this :

proc sql noprint;
create table tt (drop = row_num) as
select *, monotonic() as row_num
from readin
group by name
having row_num = min(row_num)
order by ID;
quit;

20. How to make SAS stop macro processing on Error

Ans: 1. Stop Macro Processing on Error

In the following program, we are telling SAS to stop sas code if user does not specify parameters and notifying them what they have missed. The %abort cancel; statement tells SAS to abort execution immediately.
%macro explore(inputdata= ,var=);
options notes;
%if %length(&inputdata) = 0 %then %do;
%put ERROR: INPUTDATA= must be specified;
%put ERROR: The macro ended abnormally.;
%abort cancel;
%end;

%if %length(&var) = 0 %then %do;
%put ERROR: VAR= must be specified;
%put ERROR: The macro ended abnormally.;
%abort cancel;
%end;

proc sort data = &inputdata.;
by &var.;
run;

%mend;

%explore(inputdata = , var = age );

Logic – If the length of string of a macro parameter is 0, it means the macro parameter is blank.

2. Go to End of Program If Error

In the following program, we are telling SAS to go to end of the code if error comes, The %goto statement is used to jump to end of the program.
%macro explore(inputdata= ,var=);
options notes;
%if %length(&inputdata) = 0 %then %do;
%put ERROR: INPUTDATA= must be specified;
%put ERROR: The macro ended abnormally.;
%goto exit;
%end;

%if %length(&var) = 0 %then %do;
%put ERROR: VAR= must be specified;
%put ERROR: The macro ended abnormally.;
%goto exit;
%end;

proc sort data = &inputdata.;
by &var.;
run;

%exit:
%mend;

%explore(inputdata = , var = age );

3. Check for Error after each step of SAS Code

Sometimes we make typo while entering dataset or variable name. It is important to handle these kinds of errors as well so we need to check for error(s) after each step of SAS Code (Data Step, PROCs). %if &syserr. ne 0 %then %do; works for it.
%macro explore(inputdata= ,var=);
options notes;
%if %length(&inputdata) = 0 %then %do;
%put ERROR: INPUTDATA= must be specified;
%put ERROR: The macro ended abnormally.;
%abort cancel;
%end;
%if %length(&var) = 0 %then %do;
%put ERROR: VAR= must be specified;
%put ERROR: The macro ended abnormally.;
%abort cancel;
%end;
proc sort data = &inputdata.;
by &var.;
run;
%if &syserr. ne 0 %then %do;
%abort cancel;
%end;
%mend;
%explore(inputdata = sashelp.clss , var = age );

Tip
Instead of using %length to calculate the length of macro parameter, we can use COUNTW function. It is very useful to count the number of variables in the macro parameter.

%if %sysfunc(countw(&inputdata., %str( ))) = 0 %then %do;
%abort cancel;
%end;