Commonly Asked SAS Interview Questions with Answer : 45 - 50

Ques 45 : What is the difference between INTCK and INTNX function in SAS ?

Ans : In SAS, INTCK function calculates the number of interval between to dates while INTNX function calculates the date of a given number of intervals.

Suppose today is '01JAN2015' then by INTCK we can calculate that there are '364' days in between today and '31DEC2015'.

While if we want to get the date just after '364' days from today (01JAN2015) then by INTNX the result would be '31DEC2015'.

DATA DSN;
FORMAT INTNX1 date9.;
INTCK1=INTCK('Days','01JAN2015'd,'31DEC2015'd);
INTNX1=INTNX('Days','01jan2015'd,364);
Run;



Ques 46 : Which SAS Statement is more useful or efficient : WHERE or IF ?

Ans : WHERE statement is more useful in SAS in comparison to IF statement. We can use WHERE statement in Data Step as well as with SAS Procedures but we can not use IF statement in SAS Procedures.
There are several useful operators, we can use with WHERE statement given as : 'LIKE', 'CONTAINS', 'IS NULL', 'IS MISSING', 'BETWEEN AND' and so on...

* If we use more than one 'WHERE' statements condition on same variable in a same data step or proc step, then last WHERE statement will overwrite on all previous ones

* If we use more than one 'IF' statements condition on same variable in a same data step, then there would be no execution but it could get execute by using 'OR' or 'AND'

* We can not use any automatic variables created by data step (FirstDot, LastDot ,_N_ , _ERROR_) in a 'WHERE' expression



Ques 47 : What is the SAS valid date range and what would happen if someone passes the date which is outside of SAS date range ?

Ans : We are familiar with a date '01JAN1960' which is just to perform calculation on dates into SAS.

SAS dates are valid from '01JAN1582' AD to '31DEC20000' AD. 
If we pass any date prior or after of valid dates then values would get truncated.



Ques 48 : What are the various methods to combine SAS data sets ?

Ans : There are several ways to combine SAS datasets. Some methods are given below :
(A) Concatenate (B) Interleave (C) Merge (D) Update (E) Modify



Ques 49 : What would happen if we use same variable for DROP and KEEP statement in SAS ?

Ans : If same variable is specified in both 'DROP=' and 'KEEP=' statement then variable would get dropped.



Ques 50 : While concatenation of SAS dataset, which method is faster than other : PROC APPEND or SET Statement ?

Ans : SAS Procedure PROC APPEND concatenates much faster than the SET Statement because Proc Append does not process the observations from the base data set.

* We can use any number of datasets with SET statement but for PROC APPEND we can use only two datasets

* We can specified upto 50 SAS datasets in a single SET statement

Commonly Asked SAS Interview Questions with Answer : 41 - 45

Ques 41 : Which one is most efficient : WHERE Statement or WHERE data set option ?

Ans : The 'WHERE = ' SAS dataset option is more efficient than WHERE Statement.

'Where' dataset option would transfer only those observations to PDV which match the conditional test. 

* We should not use 'FIRSTOBS=' and 'OBS=' options with WHERE Statement or WHERE option

* If both WHERE Statement and WHERE option are used in same data step then where statement would get ignored by SAS



Ques 42 : How can we create user-defined formats ?

Ans : We can create user-defined format by PROC FORMAT procedure.



Ques 43 : How could we see the physical location of any library and any dataset ?

Ans : There are some files in SAS library 'SASHELP'  by the name 'VSLIB' and 'VSTABLE' which contains the physical locations of library and datasets respectively.



Ques 44 : How do we list all SAS system options and can you list only specific one as well.

Ans : We can use PROC OPTIONS procedure to list sas system options.

To list all SAS system options :
                                                        PROC OPTIONS;
                                                        RUN;

To list specific SAS system option ;


PROC OPTIONS OPTION= ERRORS;
                          RUN;

* SAS System options would get printed into log



Ques 45 : How could we reset the SAS system option's default value to specific one ?

Ans : We can reset the SAS system option's value by using OPTIONS option.

OPTIONS ERRORS = 5;

Commonly Asked SAS Interview Questions with Answer : 36 - 40

Ques 36 : How to get the default or sample datasets or sample library into SAS.

Ans : In SAS, sample library is SAMPSIO which is set by default in configuration file. We can get the sample datasets from this library by several ways but the easiest way is :

PROC DATASETS LIBRARY=SAMPSIO;
                       RUN;



Ques 37 : How to copy an entire library to new destination with it's all attributes ?

Ans : We can use COPY statement with Proc Datasets procedure to copy an entire library.

PROC DATASETS LIBRARY= SOURCE;
                       COPY OUT = Destination;



Ques 38 : How to copy a dataset from one library to another library ?

Ans : The simplest way to copy a dataset from one library to another library is to use COPY procedure.

                PROC COPY IN = Uma OUT = Shanker;
                SELECT Dsn ;
                RUN;

* DSN dataset has been copied to 'Shanker' library from 'Uma' library.



Ques 39 : What is the specific condition when SAS does not create INPUT BUFFER ?

Ans : While creating a new dataset, if the input file is a sas dataset then SAS does not create an INPUT BUFFER. In this condition SAS writes the input data directly to the PDV.



Ques 40 : If a SAS code has too many errors, then how many errors SAS would print into its log ?

Ans : SAS prints 20 errors by default for a SAS code. We can also set the number of errors to be get printed into log by using SAS System option.

OPTIONS ERRORS = 5;

* This option is also useful to save log space. In the same way, if we don't want NOTES to print into log then we can use OPTIONS NONOTES

* There is no SAS OPTION is available to eliminate the warning messages.

Commonly Asked SAS Interview Questions with Answer : 31 -35

Quse 31 : By using PROC SQL, How could we get the correct count of data from that column which includes duplicates  ?

Ans : We can use DISTINCT keyword with an aggregate function in PROC SQL to get the count of non-duplicate data.

          PROC SQL;
          SELECT Count (DISTINCT Fname) as Count
          FROM Uma.DSN;
          QUIT;

Note : if some values are missing in Fname that would also not get counted here.



Ques 32 : What are the basic differences between HAVING and WHERE clause in PROC SQL ?

Ans : 1. Having clause works on Grouped data while Where clause affects individual rows.

2. Having Clause processes the data after the Group by clause and any aggregate functions process

3. Where clause process the data before Group by clause and any aggregate function process the data.



Ques 33 : What is the use of VALIDATE statement in PROC SQL;

Ans : In PROC SQL, VALIDATE statement is the statement which cross check the query's syntax that syntax is correct or not without submitting it.
In log, SAS displays about the syntax whether it is correct or not.

                  PROC SQL;
                  VALIDATE SELECT Name, EmpID
                  FROM Uma.DSN;
                  QUIT;



Ques 34 :  What do you understand by Join or Joining the tables in PROC SQL ?

Ans : Joining Tables gives us the facility to fetch the data from two or more tables. It does not alter the original tables.



Ques 35 : What are the effects of Null values in JOINS in PROC SQL ?

Ans : PROC SQL behaves with Null values like missing values and same in joins. 
It means if we specify any null value in PROC SQL join then it would be matched with other null value of the same type of it's own (Character or numeric ).

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.