Commonly Asked SAS Interview Questions with Answer : 26 -30

Ques 26 : While joining two tables by PROC SQL in SAS, results are not up to the mark due to some missing values. How can we assign any specific value to these missing values ?

Ans : We can use COALESCE function in PROC SQL to replace missing values of a column by a value which we can specify. 
OR
COALESCE function is to replace missing values of a column with any specified value.
We can also replace missing values of a column by using CASE Expression in PROC SQL.

PROC SQL;
SELECT EmpID, COALESCE (Emp_Name, 'Uma Shanker') as Emp_Name
FROM Uma.DSN;
QUIT;

PROC SQL;
SELECT EmpID, 
CASE when Emp_Name is missing then 'Uma Shanker' 
ELSE Emp_Name
End as Emp_Name
FROM Uma.DSN;
QUIT;

•  In above examples, we have replaced missing value of column Emp_Name to Uma Shanker.

Ques 27 : What does ORDER BY do in PROC SQL ? Is this similar to PROC SORT's BY statement ?

Ans : We use ORDER BY in PROC SQL to sort the data or result table to appear in a particular order with respect to the column name.

No, ORDER BY is not similar to the PROC SORT with BY statement. As Proc Sort changes the primary or base table but ORDER BY in PROC SQL only changes the output result of table.



Ques 28 : Can we sort or ORDER BY the query result table in PROC SQL by columns which are not included in SELECT statement ?

Ans : Yes, We can ORDER BY the query result by columns which are not included in SELECT statement.

                             PROC SQL;
                             SELECT Emp_Name
                             FROM Uma.DSN
                             ORDER BY EMPID;
                             QUIT;



Ques 29 : What would happen, if we use ORDER BY clause in PROC SQL with respect to that column which contains missing values ?

Ans : In SAS, ORDER BY clause in PROC SQL sorts missing values or null values first, before sorting the character or numeric data.



Ques 30 : Is there any chance in SAS, When PROC SQL automatically changes it's GROUP BY clause to ORDER BY clause ?

Ans : Yes, When we do not specify any aggregate function with GROUP BY clause then PROC SQL automatically changes the GROUP BY clause into ORDER BY clause.
We can see the warning message into log related to this.

PROC SQL;
SELECT Emp_Name, EmpID
FROM Uma.DSN
GROUP BY Emp_Name;
QUIT;

Warning in log : A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause not the optional HAVING clause of the associated table-expression referenced a summary function.

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;

Commonly Asked SAS Interview Questions with Answer : 16 -20

Ques 16 : Could you write a data step to create a SAS dataset with minimum use of SAS rules and letters?

Ans :      Data ;          
                 X=1;       
                 Run;



Ques 17 : Why have you not written SAS dataset name in above answer ? Would it get run or not ?

Ans :  The above example would get run as if we don't provide the sas dataset name in data step then SAS would use its by default sas dataset names that are 'DATA1, DATA2........DATAn.



Ques 18 : What would happen if I write "DATE" in place of Data in answer 16 ? It's just a typo mistake. Would it get run or not ? 'Yes' / 'No'... Explain.

Ans : It would get run with a warning like 'Assuming the symbol DATA was misspelled as DATE'.

for more info : Types of ERRORs in SAS



Ques 19 : What is _NULL_ and why have you not used this for question 16 ? What about the _LAST_ ?

Ans : You have asked to create a SAS dataset in above question and of course minimum letters of English.

_NULL_ is the default keyword in SAS which execute a datastep but does not create any dataset so i have not used it.

_LAST_ defines the most recently created SAS dataset. If we execute any Data step or Proc step without specifying any input dataset then by default SAS uses the _LAST_.



Ques 20 : Where we can use SAS datasets name ? How ? When ? Why ?

Ans : We named SAS dataset name when we need to create them.
If we want to create an output dataset in data step then we need to provide it in DATA Statement.

If we want to create an output dataset in Proc step then we need to name it in Procedure or in Output Statement.

If we don't provide any data set name then SAS would use it's default dataset names.

We can use SAS dataset name with following Statements, Procedures, Function and Options:

A.  Data Statement
B.  Set Statement
C.  Merge Statement
D.  Update Statement
E.  Modify Statement
F.  Data = DSN ( as option of any SAS procedure )
G. Open function

Whenever we create sas view, we also name SAS dataset name in SAS Procedure ( in PROC SQL ) and View = Option for Data step.





* Today I played both the role ( Interviewer and Candidate ), If you feel that answers are not completely polite in nature. I just tried to show some attitude in nature. Candidate is there to get the job not to beg the job. Attitude matters, it shows your confidence,But be POLITE.