Advanced SAS Interview Questions and Answers Part – 1

01. Two ways to select every second row in a data set

Ans: data example;
set sashelp.class;
if mod(_n_,2) eq 0;
run;

MOD Function returns the remainder from the division of the first argument by the second argument. _N_ corresponds to each row. The second row would be calculated like (2/2) which returns zero remainder.

data example1;
do i = 2 to nobs by 2;
set sashelp.class point=i nobs=nobs;
output;
end;
stop;
run;

02. How to select every second row of a group

Ans: Suppose we have a table sashelp.class. We want every second row by variable ‘sex’.

proc sort data = sashelp.class;
by sex;
run;
data example2 (drop = N);
set sashelp.class;
by sex;
if first.sex then N = 1;
else N +1;
if N = 2 then output;
run;

03. How to calculate cumulative sum by group

Ans: Create Sample Data

data abcd;
input x y;
cards;
1 25
1 28
1 27
2 23
2 35
2 34
3 25
3 29
;
run;

How to calculate cumulative sum by group
Cumulative Sum by Group

Cumulative Sum by X

data example3;
set abcd;
if first.x then z1 = y;
else z1 + y;
by x;
run;

04. Can both WHERE and IF statements be used for subsetting on a newly derived variable?

Can both WHERE and IF statements be used for subsetting on a newly derived variable?
SAS : WHERE vs. IF

Ans: No. Only IF statement can be used for subsetting when it is based on a newly derived variable. WHERE statement would return an error “newly derived variable is not on file”.

Please note that WHERE Option can be used for subsetting on a newly created variable.

data example4 (where =(z <=50));
set abcd;
z = x*y;
run;

05. Select the Second Highest Score with PROC SQL

Ans: data example5;
input Name $ Score;
cards;
sam 75
dave 84
sachin 92
ram 91
;
run;

proc sql;
select *
from example5
where score in (select max(score) from example5 where score not in (select max(score) from example5));
quit;

06. Two ways to create a macro variable that counts the number of observations in a dataset

Ans: data _NULL_;
if 0 then set sashelp.class nobs=n;
call symputx(‘totalrows’,n);
stop;
run;
%put nobs=&totalrows.;

proc sql;
select count(*) into: nrows from sashelp.class;
quit;
%put nobs=%left(&nrows.);

07. Suppose you have data for employees. It comprises of employees’ name, ID and manager ID. You need to find out manager name against each employee ID.

Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager name against each employee ID.
SQL: Self Join

Create Sample Data

data example2;
input Name $ ID ManagerID;
cards;
Smith 123 456
Robert 456 .
William 222 456
Daniel 777 222
Cook 383 222
;
run;
SQL Self Join
proc sql;
create table want as
select a.*, b.Name as Manager
from example2 as a left join example2 as b
on a.managerid = b.id;
quit;

Data Step : Self Join

proc sort data=example2 out=x;
by ManagerID;
run;
proc sort data=example2 out=y (rename=(Name=Manager ID=ManagerID ManagerID=ID));
by ID;
run;
data want;
merge x (in= a) y (in=b);
by managerid;
if a;
run;

08. Create a macro variable and store TomDick&Harry

Ans:
Issue : When the value is assigned to the macro variable, the ampersand placed after TomDick may cause SAS to interpret it as a macro trigger and an warning message would be occurred.

%let x = %NRSTR(TomDick&Harry);
%PUT &x.;

%NRSTR function is a macro quoting function which is used to hide the normal meaning of special tokens and other comparison and logical operators so that they appear as constant text as well as to mask the macro triggers ( %, &).

09. Difference between %STR and %NRSTR

Ans: Both %STR and %NRSTR functions are macro quoting functions which are used to hide the normal meaning of special tokens and other comparison and logical operators so that they appear as constant text. The only difference is %NRSTR can mask the macro triggers ( %, &) whereas %STR cannot.

10. How to pass unmatched single or double quotations text in a macro variable

Ans: %let eg = %str(%’x);
%let eg2 = %str(x%”);
%put &eg;
%put &eg2;

If the argument to %STR or %NRSTR contains an single or double quotation mark or an unmatched open or close parenthesis, precede each of these characters with a % sign.