01. Two ways to select every second row in a data set
Ans: data example;
if mod(_n_,2) eq 0;
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.
do i = 2 to nobs by 2;
set sashelp.class point=i nobs=nobs;
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;
data example2 (drop = N);
if first.sex then N = 1;
else N +1;
if N = 2 then output;
03. How to calculate cumulative sum by group
Ans: Create Sample Data
input x y;
Cumulative Sum by X
if first.x then z1 = y;
else z1 + y;
04. Can both WHERE and IF statements be used for subsetting on a newly derived variable?
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));
z = x*y;
05. Select the Second Highest Score with PROC SQL
Ans: data example5;
input Name $ Score;
where score in (select max(score) from example5 where score not in (select max(score) from example5));
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;
select count(*) into: nrows from sashelp.class;
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.
Create Sample Data
input Name $ ID ManagerID;
Smith 123 456
Robert 456 .
William 222 456
Daniel 777 222
Cook 383 222
SQL Self Join
create table want as
select a.*, b.Name as Manager
from example2 as a left join example2 as b
on a.managerid = b.id;
Data Step : Self Join
proc sort data=example2 out=x;
proc sort data=example2 out=y (rename=(Name=Manager ID=ManagerID ManagerID=ID));
merge x (in= a) y (in=b);
08. Create a macro variable and store TomDick&Harry
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);
%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%”);
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.