Commonly Asked SAS Interview Questions with Answer : 21 -25

Ques 21 : How to limit the number of rows / observation in PROC SQL ?

Ans : We can limit the number the rows in PROC SQL by using 'OUTOBS=n' option which is similar to 'OBS=n' SAS dataset option.



Ques 22 : How to eliminate / remove the duplicate rows in SAS using PROC SQL ?

Ans : We can use 'DISTINCT' keyword in SELECT clause to remove duplicates in SAS by using PROC SQL.



Ques 23 : How 'DISTINCT' works in SAS PROC SQL, Is it check for only one column or all ?

Ans :  DISTINCT keyword removes the duplicates and removal depends on the specified column or columns. 
If we specify all the columns of the table in SELECT clause with DISTINCT keyword the PROC SQL would remove duplicate rows or row for which values of all the columns match.

In Simple word : DISTINCT checks for all the columns in table, if matches then removes.

                         PROC SQL;
                         SELECT DISTINCT Name
                         FROM Uma.DSN;
                         QUIT;



Ques 24 :  How to determine the structure of the table in SAS using PROC SQL ? If dataset has been created by datastep then same statement would work or not ?

Ans : To get the structure of the table in SAS using PROC SQL we need to use 'DESCRIBE TABLE' statement.
It writes the description of the table into the log.
DESCRIBE TABLE statement works for those SAS dataset as well, which were created by data step.

                    PROC SQL;
                    DESCRIBE TABLE  Uma.DSN;
                    QUIT;



Ques 25 :  Can we refer calculated column or columns within the same PROC SQL query for calculation ?

Ans : Yes, We can refer calculated column or columns within the same PROC SQL query using 'CALCULATED' keyword with respect to alias of that column.
Even we can use calculated columns with WHERE and ORDER BY clause as well.

PROC SQL;
SELECT EmpId, F_Sal * 2 as Fe_Sal, M_Sal * 2 as Ma_Sal,
(CALCULATED Fe_Sal - CALCULATED Ma_Sal) as EMP_Sal
FROM Uma.DSN;
QUIT;

Output Column For Number Of Duplicate Rows In SAS

Suppose we have a table or SAS data set that contains duplicate rows/ observation.

We just want to calculate / count the number of duplicate rows from that table / SAS data set .

It would be great if we can generate an output column which could show that how many times each row occurs.

Example : 

Suppose we have a table / SAS data set that contain two variable / column known as last name (Lname) and first name (Fname) showing as :

                                                   Data DSN;

                                                  input Fname $ 1-7 Lname $ 8-15 ;

                                                  Cards;

                                                  Rebecca Smith

                                                  Kathy   John

                                                  Rebecca Smith

                                                  Tom     Andrew

                                                  Reed    Samuel

                                                  Rebecca Smith

                                                  Kathy   John

                                                  Reed    Samuel

                                                  Davis   Kerry

                                                  ;

                                                  Run;

The output would be like that :

To generate the number of duplicate column from the table :

                                                    Proc SQL;

                                                   Select *, count (*) as count 

                                                   From DSN

                                                   Group by Fname, Lname 

                                                   Having count (*) > 1;

                                                   Quit;

and the result would be as :

* DSN is the name of the SAS data set.