Advanced SAS Interview Questions and Answers

These are the most frequently asked Advanced SAS Interview Questions and Answers. It covers topics on PROC SQL, SAS Macros, Advanced Data Manipulation case studies. These questions are best suited for interviews for SAS Developer, SAS Programmer role. It includes some common tricky and tough questions that are generally asked in an interview.

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.

8. Create a macro variable and store TomDick&HarryIssue : 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 ( %, &).

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

%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.

11. How can we use COUNTW function in a macro

%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?

&&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

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

SAS : Reference Macro Variable

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

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

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.

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

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.

The DICTIONARY.COLUMNS contains information such as name, type, length, and format, about all columns in the table. LIBNAME : Library Name, MEMNAME : Dataset Name

%put variables = &vars.;

25. How would DATA STEP MERGE and PROC SQL JOIN works on the following datasets shown in the image below?

Many to Many Merge

The DATA step does not handle many-to-many matching very well. When we perform many to many merges. the result should be a cartesian (cross) product. For example, if there are three records that match from one contributing data set to two records from the other, the resulting data set should have 3 × 2 = 6 records. Whereas, PROC SQL creates a cartesian product in case of many to many relationship.

The above list of SAS interview questions are designed especially for experienced SAS programmers and analysts. These are real world examples with proper explanation. Most of the tough SAS interviews include SAS SQL and Macros questions. Before going for interview, you need to brush up your concepts of SAS programming. It is advised to go through concepts when practicing above interview questions.

Love this Post? Spread the Word

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Hi Deepanshu.. Your blogs are really helpful. I have finished by Base Programming certification with 98%. I am put up in Bangalore. Can you help me with companies looking for SAS candidates? Thanks in Advance

use data in the Data Sheet 1. Consider you have Sales Data and a variable Order_date is the Date of Order Placed. Write a Macro that will Display the Report of all the Sales Conducted on Daily Basis ?

Example: If today is 01 January 2000, If I call macro today, It shows orders placed on 01 January 2000. If I call same macro on 02 January, It should give Report of orders placed on 02 January.

proc print data=Sales_Data;

where Order_date='01January2000'd;

title “Below are the details of Sales data for 01-January-2000”;

Run;

2. Consider Same Sales Data, Write a Macro to get summarized report on any particular year ? Example: I want to run proc means on sales data for the year 2004, may be tomorrow I want to get for 2005 or so.

proc print data=Sales_Data;

where Order_date='2004'd;

Run;

3. Consider the Same Sales Data. Suppose you have 3 Types of Orders and a variable Order_type having values either Type 1 Type 2 or Type 3 for Each Order Placed and you have another variable Quantity, having the total number of quantities ordered for each order. Write a Macro to display the frequency of quantity ordered for given order type in any particular year. Example: If I mention value of year 2004 and type 3, It should display frequency of quantities placed in 2004 with type 3 with appropriate title. If I just mention year 2004, It should display frequency of all quantities ordered in 2004 with all types.

4. Write a Macro to get pdf report of orders placed in range of years with appropriate title? Example: If I select range of years 2003 to 2006, I should get pdf report of orders placed in all these years separately on desktop.

5. Write a Macro to get a Report for each year and each order type, so we get the mean of total_retail_price in the title ?

Example: Suppose I choose year = 2001 and order_type = 2, It should display orders placed in these parameters and I also want the mean of total_retail_price in the title of the report.

6. Suppose you are having live Data, Orders coming from different Countries. Write a Macro to split this master Data into smaller Datasets depending on the Unique value of each countries ?

Example: Suppose I got Order placed from US, CA Today, so I call the macro and it will split the master data into 2 data US and CA having those observation. Tomorrow suppose I have order placed from CH, JP and AU, I call same macro and it will split master data into 3 smaller Data.

1. Consider you have Sales Data and a variable Order_date is the Date of Order Placed. Write a Macro that will Display the Report of all the Sales Conducted on Daily Basis ?

Example: If today is 01 January 2000, If I call macro today, It shows orders placed on 01 January 2000. If I call same macro on 02 January, It should give Report of orders placed on 02 January.

- 2. Consider Same Sales Data, Write a Macro to get summarized report on any particular year ? Example: I want to run proc means on sales data for the year 2004, may be tomorrow I want to get for 2005 or so.

- 3. Consider the Same Sales Data. Suppose you have 3 Types of Orders and a variable Order_type having values either Type 1 Type 2 or Type 3 for Each Order Placed and you have another variable Quantity, having the total number of quantities ordered for each order. Write a Macro to display the frequency of quantity ordered for given order type in any particular year. Example: If I mention value of year 2004 and type 3, It should display frequency of quantities placed in 2004 with type 3 with appropriate title. If I just mention year 2004, It should display frequency of all quantities ordered in 2004 with all types.

- 4. Write a Macro to get pdf report of orders placed in range of years with appropriate title? Example: If I select range of years 2003 to 2006, I should get pdf report of orders placed in all these years separately on desktop.

- 5. Write a Macro to get a Report for each year and each order type, so we get the mean of total_retail_price in the title ?

Example: Suppose I choose year = 2001 and order_type = 2, It should display orders placed in these parameters and I also want the mean of total_retail_price in the title of the report.

proc sql;select count(a.product) into:Total_Order from sales as a left join order as bon a.order_id=b.order_idwhere b.order_date=&daily;quit;

%put &Total_Order;

Title 'Find the total sales as on &daily is &Total_Order';

- 2. Consider Same Sales Data, Write a Macro to get summarized report on any particular year ? Example: I want to run proc means on sales data for the year 2004, may be tomorrow I want to get for 2005 or so.

3. Consider the Same Sales Data. Suppose you have 3 Types of Orders and a variable Order_type having values either Type 1 Type 2 or Type 3 for Each Order Placed and you have another variable Quantity, having the total number of quantities ordered for each order. Write a Macro to display the frequency of quantity ordered for given order type in any particular year. Example: If I mention value of year 2004 and type 3, It should display frequency of quantities placed in 2004 with type 3 with appropriate title. If I just mention year 2004, It should display frequency of all quantities ordered in 2004 with all types. ;

%macro freq(year,type);proc sql;select year,quant,order_type from (select distinct year(sales_date) as year,order_type,sum(qty) as quant from sales group by order_type) where year=&year and order_type=&type;quit;%mend;