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.