SAS Interview Question with Answer : 86 and 87

Ques 86 : 
Suppose we have a dataset (One) which contains 100 numeric variables like V1, V2, V3.....V100. Create a new dataset (Two) with new variables N1, N2, N3,.....N98 with following condition :
N1 - Stores the sum of value V1, V2, V3
N2 - Stores the sum of value V2, V3, V4
N3 - Stores the sum of value V3, V4, V5
.
.
.
.
N98 - Stores the sum of value V98, V99, V100.


Ans : Firstly i have created dataset One with following array code :

and the output of dataset one is as below :

Now code for desired dataset (Two):

and the output is as desired :

Ques 87 : What would happen, if we don't supply the third argument (Length) in SUBSTR function ?

Ans :  If we omit the third argument from SUBSTR function then in result, it would return all the characters from the string, starting from the starting position (Second argument) of SUBSTR function.

We can use following syntax : 

Output :

Commonly Asked SAS Interview Questions with Answer : 81 - 85

Ques 81 : What is the way to insert a blank line after every n observation in SAS while listing a report ?

Ans : As we know that the default reports are difficult to read but it would be easier to read if a blank line would get inserted after every few observations.

We can insert a blank line by using BLANKLINE option in Proc Print statement.

Suppose we want to insert a blank line after every five observation then we can use following syntax :

Ques 82 : How could we filter the data based on multiple values for the same variable ?

Ans : We can filter the data on multiple values for the same variable by using OR or IN operator.

Syntax for OR operator : 

Syntax for IN operator :

• We would get the same output by using OR Operator or IN Operator

Ques 83 : How could we filter the missing data even if we are not sure whether the variable is character or numeric ?

Ans :  If we are not aware about the data type of the variable and we need to filter it by missing observations, we can use IS MISSING with WHERE Statement in SAS.

Ques 84 : How to create a SAS dataset which consists all SAS system options which are currently in effect ?

Ans : We have two ways to get the list of all SAS System options which are currently in use.

1. By using SAS Procedure :

2. By dictionary tables : 

Quse 85 : How could we control the delimiter within the a Date ?

Ans : To control the delimiter within the Date, we have specific SAS Date Formats. We can use desired SAS Date format for required output. 

For Example, if we want to apply Colon (:) delimiter within SAS Date then we can use DDMMYYC10. Date format.

some more delimited SAS Date formats are :

• For Slash (/) : MMDDYY10.
• For Dash (-) : DDMMYYD10.
• For Point (.) : DDMMYYP10.

Commonly Asked SAS Interview Questions with Answer : 76 - 80

Ques 76 : What is the difference between file extension .SAS and .LST in SAS ?

Ans : File extension is an identifier for type of file. There are several types of file extensions for SAS files. I am just specifying some of them.

1. x.SAS                  :  SAS Program file
2. x.CFG                 : SAS Configuration file
3. x.LST                  : SAS Output file
4. x.LOG                 : SAS Log file
5. x.SAS7BPGM    : SAS Stored Program file
6. x.SAS7BDAT     : SAS Data Set file
7. x.SAS7BVEW    : SAS Data Set View file
8. x.SAS7BCAT     : SAS Catalog file
9. x.SAS7BUTL     : SAS Utility file
10. x.SAS7BNDX    : SAS Data Set Index file

• x is a name of file

Ques 77 : What is the default action of Frequency Procedure (Proc Freq) ?

Ans :  The default action of Frequency Procedure is to calculate frequencies on each variable irrespective of character or numeric of defined SAS data set.  

It means we can use PROC FREQ to compute frequencies for both character and numeric variables.

By default Proc Freq calculates four statistics on each variable which are Frequency, Percent, Cumulative Frequency and Cumulative Percent.

• Proc Freq is not only a descriptive procedure as well as a statistical procedure which produces one way and n-way frequency tables

Ques 78 : How to get the output in List format rather than as a Cross Tabulation in Proc Freq ?

Ans : We can specify LIST option in Proc Freq to print the table in list format.

Ques 79 : How do we get to know that which SAS products are licensed and Installed at our machine ?

Ans : We can use Setinit Procedure (Proc Setinit) to get to know about licensed SAS products for our machine. 

Most of the time, we may have some SAS products licensed but not installed so to get to know only installed products we can use Product_Status Procedure (Proc Product_status).

Syntax : 

Ques 80 : How could we count the number of missing values of numeric variable in SAS ?

Ans : We can count the number of missing values for numeric variables by using NMISS option in PROC MEANS. 

Proc Means requires at least one numeric variable to calculate statistics.

Syntax : 

Commonly Asked SAS Interview Questions with Answer : 71 - 75

Ques 71 : What is the difference between OUTPUT and PUT statement in SAS ?

Ans : PUT Statement is to write the variable values or text strings to an external file or SAS log or SAS output window in SAS while OUTPUT Statement is to write observations to a SAS dataset.



Ques 72 : What are the table look-up techniques in SAS (any four) ?

Ans : There are four table look-up techniques in SAS :

1. Match Merge of two sas data sets
2. Format tables
3. Arrays
4. Direct Access by using Point option

Ques 73 : Can we use Global Statements anywhere in SAS ? How many Global Statements have you used ?

Ans : Yes, We can use Global Statements anywhere is SAS program. I have used several Global Statements and some names are as given below :

1. FILENAME    : To access data file
2. LIBNAME      : To access SAS data libraries
3. FOOTNOTE  : To write 10 foot note lines
4. TITLE            :  To write title lines
5. COMMENT   : To specify the purpose of program
6. %INCLUDE   : To include files of SAS statements
7. %LIST           : To list lines from current session
8. OPTIONS      : To specify the SAS system options
9. RUN              : To execute the previously entered SAS statements
10. SKIP              : To create a blank line in SAS log

Ques 74 :  Can we use multiple SAS directories or libraries in a single Libname Statement ?

Ans : Yes, We can use multiple SAS directories or libraries in a single Libname Statement.

Kindly refer : Library Concatenation



Ques 75 : How could we read an external file which contains long lines ? or What is the use of LRECL in Infile / File SAS Statement ?

Ans : We can use LRECL (Logical Record Length) SAS system option to read or write the lines longer than 256 character.

As we know that logical record length for Windows and Unix systems is 256 bytes, to read or write the long data we can specify LRECL with Infile or File Statement in SAS.

• Minimum and Maximum LRECL is 1 and 32767 bytes respectively

Commonly Asked SAS Interview Questions with Answer (ST) : 11 - 15

Ques (ST) 11 : What are the different types of SAS Application Server's required for SAS Enterprise Guide to run ?

Ans : SAS Enterprise Guide interacts with different types of SAS Application Server's to perform all activities which are mentioned below :

1. SAS Metadata Server : To read and write metadata to SAS metadata repository
2. SAS OLAP Server : To process queries for cubes
3. SAS Stored Process Server : To execute store processes
4. SAS Workspace Server : To execute SAS codes and to access data

Ques (ST) 12 : Can we create CUBES by using SAS Enterprise Guide now ?

Ans : No, Now we cannot create CUBES by using SAS Enterprise Guide.

We can only view cubes by using OLAP Analyzer in SAS Enterprise Guide.

• We can build cubes by using SAS OLAP CUBE Studio or SAS DI Studio

Ques (ST) 13 : What are the different types of SAS Application Server's required for SAS Add-In Microsoft Office ?

Ans : SAS Add-In Microsoft office interacts with following SAS Application Server's :

1. SAS Metadata Server
2. SAS Stored Process Server
3. SAS Workspace Server

• SAS Add-In Microsoft office does not communicate with SAS OLAP Server but Microsoft Excel can access SAS OLAP cubes by using pivot table facility which is in-built in MS Excel

Ques (ST) 14 : What are the different types of SAS Application Server's required for SAS Information Map Studio ?

Ans : SAS Information Map Studio interacts with following SAS Application Server's :

1. SAS Workspace Server
2. SAS Metadata Server
3. SAS OLAP Server

Ques (ST) 15 : What are SAS Information maps and who uses it ? What is the basic role of SAS Information Map Studio ?

Ans : SAS Information Maps includes metadata which explains data warehouse in business terminology. 
It displays the data in business term with labels in spite of tables and columns name as in data warehouse to business user.

SAS Information Maps consists physical tables, relations between tables and business rules.

Business users use Information Maps.

SAS Information Map Studio works like a bridge between data warehouse and business user who builds reports from data.

Commonly Asked SAS Interview Questions with Answer (ST) : 6 - 10

Ques (ST) 6 : What is SAS Stored Process and what are the uses of it ?

Ans : SAS Stored Process is a SAS code or program which is stored on a server and could be executed by many SAS applications. 

SAS Stored Process is described by metadata and gives effective method for change control management as program is saved on server. 

Stored Processes can be centrally managed and maintained which provides the facility to update the program and at the same time all SAS clients which invokes the program would get result from updated version of code.

We can use SAS Stored Process for the following :

1. To build web applications
2. Analytics
3. Web reporting
4. To deliver result package to clients or to Middle tier
5. To publish results
6. Centrally management of program
7. To enhance the security of program and data

Ques (ST) 7 : What are the Inputs and Outputs for SAS Stored Process ?

Ans : SAS Stored Process could access any SAS data source or external file to create SAS dataset, files and all other data targets supported by SAS.

INPUTS to SAS Stored Process :

1. SAS Data sources
2. External files

OUTPUTS from SAS Stored Process :

1. External files
2. SAS Datasets
3. Catalogs
4. E-mails
5. Result packages
6. ODS outputs

Ques (ST) 8 : What are the SAS Web Clients for SAS Stored Process ?

Ans : We can access or execute SAS Stored Process by following SAS Web Clients :

1. SAS Web Report Studio
2. SAS Information Delivery Portal
3. Stored Process Web Application

Ques (ST) 9 : What are the servers on which SAS Stored Processes are hosted ?

Ans : SAS Stored Processes can be hosted by two types of servers :

1. SAS Stored Process Server
2. SAS Workspace Server

Ques (ST) 10 : What is your preferred method to work on (Create) SAS Stored Processes ?

Ans : For me, SAS Enterprise Guide is the preferred method to work on SAS Stored Processes because it gives the facility to create, register and test stored processes in one interface.

SAS Data Integration Studio also gives a point and click interface for creating and registering Stored Process.

• SAS Stored Processes must be .sas file in SAS.

Commonly Asked SAS Interview Questions with Answer (SDW) : 1

Ques (SDW) : How many different types of SQL's statements are we use mainly ?

Ans : There are six types of SQL's Statements but mainly we refer to THREE types only.

• DDL : Data Definition Language
• DML : Data Manipulation Language
• DCL : Data Control Language

Commonly Asked SAS Interview Questions with Answer (ST) : 1 - 5

Ques (ST) 1: What are the basic steps for data mining in SAS Enterprise Miner ?

Ans : The basic step for data mining in SAS Enterprise Miner is SEMMA. It is a short form of five processes which are given below :

• Sample   (S) 
• Explore   (E)
• Modify    (M)
• Model     (M)
• Access   (A)

Ques (ST) 2: What is SAS Add-In for Microsoft Office ?

Ans : The SAS Add-In for Microsoft Office is a Component Object Model (COM) which provides access to SAS Datasets from MS-Excel, MS-Power Point, MS-Word and MS Outlook.

It also allows SAS Stored Processes to be run on server and the result to be returned to either MS-Word or MS-Excel.



Ques (ST) 3: What is Change Management feature in SAS DI Studio ?

Ans : The Change Management feature in SAS DI Studio allows multiple users to work on same metadata repository at the same time without overwriting each other's change.

• If Change Management is enabling for specific user then user could see pop-up with Check-in and Check-out.

• If a metadata object has been checked out by one user, it means that metadata object is locked so that it could not be updated by other user until the specific object has been checked back in by that user.

Ques (ST) 4: What is SAS OLAP Cube Studio ?

Ans : The SAS OLAP Cube Studio is to build and define OLAP Cubes in SAS. It is a Java interface tool.

OLAP - Online Analytical Processing is a software technology which allows us to analyze the data from multidimensional database tables dynamically.

CUBE - A Cube is a specific set of data which is organized, structured in a hierarchical and multidimensional manner with various dimensions and stages of data. Cubes also contains measures which are numeric analysis of input tables



Ques (ST) 5: What are the SAS Stored Process Desktop Clients ?

Ans : There are many Desktop clients by which we can access or execute the SAS Stored Process.
Few names are given below :

1. SAS Enterprise Guide
2. SAS Information Map Studio
3. SAS Add-In for Microsoft Office

Commonly Asked SAS Interview Questions with Answer : 66 - 70

Ques 66 : Is it possible that we could sandwich INSERT and DELETE or any other statements in between PROC SQL and QUIT statement ?

Ans : Yes, We could sandwich INSERT and DELETE or any other statements in between PROC SQL and Quit statement.

Syntax :
PROC SQL;
DELETE from DSN

WHERE Id="2458";

INSERT into DSN

Select * from Uma.DSN

WHERE ID="2458";

QUIT; 



Ques 67 : How many tables can we join in a single INNER JOIN in PROC SQL and what could be the maximum length of a macro variable ?

Ans : 32

• We can combine a maximum of 32 tables in a single INNER Join
• Maximum length for macro variable is 32 character

Ques 68 : What is the difference between INPUT Statement and INPUT Function in SAS?

Ans : The INPUT Statement is used to define the variables while INPUT Function converts character values to numeric values (by using informat).



Ques 69 : How to get the defined range of observations from a SAS Dataset ?

Ans : To get the observation of a define range from a SAS Dataset, we can use SAS dataset options FIRSTOBS= and OBS=.

Suppose we have a dataset with 100 observations but we just want to print observation from 50 to 55 then we need to specify FIRSTOBS and OBS dataset options.

• FIRSTOBS= : Specifies the starting point of desired range
• OBS= : Specifies the ending point of desired range

 
Ques 70 : What is the use of Single dash (-) and Double dash (--) in between variables in SAS like Var V1-V4 and Var V1--V4 ?

Ans : When variables of a SAS dataset are in successive naming convention with same prefix and we want to get all or few of them, we can use single dash (-) or double dash (--) to select the variables.

Single Dash (-) : When variables names are in same prefix with successive suffix of number, we can use single dash to specify the entire range of variables. It would exclude all other variables which don't have same prefix.

Double Dash (--) : When we want to get a list of variables from a dataset in order in which they are specified in dataset, we can use double dash (--). It would include all other variables which don't have same prefix as well.

Commonly Asked SAS Interview Questions with Answer : 61 - 65

Ques 61 : How could we create a SAS date value by using separate variables representing day, month and year of the date ?

Ans : We can use MDY function to create a SAS date value by using separate variables like day, month and year.

Ques 62 : What are the ways to view cube data when cube has been created by SAS OLAP CUBE Studio ?

Ans : We can view the cube data (cube - created by SAS OLAP CUBE STUDIO ) by following ways :

1.  SAS Enterprise Guide

2.  SAS Web OLAP viewer for java

3.  MS- Excel Pivot tables

4.  SAS Information Delivery Portal's visual data explorer portlet

Ques 63 : What are the load styles or ways to load the data to DW or permanent table by using TABLE LOADER transformation in SAS DI Studio ?

Ans : There are THREE ways to load the data to permanent table by using Table Loader Transformation in SAS DI Studio as given below :

1. Append to Existing

2. Replace

3. Update / Insert

Ques 64 : Can we create an Index on a View ?

Ans : No, We can not create an Index on View.

Ques 65 : Why we do Indexing (in simple word) and how many types of Indexes we can create in SAS ?

Ans : Indexing : To extract a small subset of observations from a large data set.

We can create two types of Indexes in SAS :

1. Simple Index

2. Composite Index

Commonly Asked SAS Interview Questions with Answer : 56 - 60

Ques 56 : What is the use of MEANS Procedure in SAS and what are the defaults statistics for it ?

Ans : The MEANS Procedure (PROC MEANS) is to get summarized data by computing descriptive statistics for numeric variables on across all observations.

PROC MEANS produced five statistics by default which are given below :
1. N (Number of non-missing values)
2. MEAN
3. STD DEV (Standard Deviation)
4. MINIMUM
5. MAXIMUM



Ques 57 : What are the basic difference between PROC MEANS and PROC SUMMARY in SAS ?

Ans : PROC MEANS with NOPRINT option is just like as PROC SUMMARY.

The basic difference between both is : By default, PROC SUMMARY creates an output dataset while PROC MEANS produces a printed output.



Ques 58 : What are the NULL data sets and DEFAULT data sets in SAS ?

Ans : There are three types of special SAS Data Sets in SAS :
1. Null Data sets : _NULL_

2. Default Data sets : _LAST_

3. Automatic naming convention Data sets : DATAn

* for DATAn, Kindly refer : Question 17



Ques 59 : What is the use of PUT and INPUT function in SAS ?

Ans : PUT Function : It converts numeric values to character values and it requires FORMAT.

INPUT Function : It converts character values to numeric values and it requires INFORMAT.

Proc contents output of Data set R:

Ques 60 : How can we count the number of missing values of numeric variable in a SAS data set ?

Ans : To count the number of missing values for numeric variable, We could use MEANS Procedure with NMISS option.


PROC MEANS DATA=DSN NMISS;
VAR NUMV;
RUN;

Commonly Asked SAS Interview Questions with Answer : 51 - 55

Ques 51 : How could we create an empty table which should have all the attributes of an existing table ?

Ans : By using LIKE clause in PROC SQL, we can create an empty table which would have all the attributes of an existing table.

PROC SQL;
CREATE TABLE DSN1
LIKE DSN;
QUIT;

* DSN1 and DSN are tables



Ques 52 : While using SQL Procedure in SAS, What is the difference between 'SET' clause and 'VALUE' clause ?

Ans : For SQL Procedure in SAS, we use 'SET' clause and 'VALUE' Clause to insert the rows / value into the table.

By using SET clause we can assign values to column by name irrespective of column position while VALUE clause assigns values to column by column position.

* For SET clause, if we don't specify the data for a column then value of that column would be a missing value

* For VALUE clause, if we don't specify the data for a column then there would be an error : Row is not inserted



Ques 53 : What is 'TRANS_RC' and 'JOB_RC' in SAS DI Studio ?

Ans : TRANS_RC and JOB_RC are job's status handling macro variables. 
we can set &Trans_rc and &Job_rc macro variables with respect to the return code value of completed transformation and individual steps within a transformation respectively in SAS DI Studio.



Ques 54 : What is the difference between '%STR' and '%BQUOTE' SAS macro functions ?

Ans : %STR and %BQUOTE macro functions are for masking the special characters and Mnemonics. 

We can use %STR macro function is specifically for unmatched quotation marks and parentheses while we use %BQUOTE macro function for matched quotation marks and parentheses.

Macro function %STR is a compilation function while %BQUOTE is execution function.



Ques 55 :  What is the difference between '%EVAL' and '%SYSEVALF' SAS macro functions ?

Ans : SAS Macro functions %EVAL and %SYSEVALF are to evaluate the arithmetic expression.

%EVAL function supports only integer arithmetic expressions while %SYSEVALF function supports floating point values for arithmetic expressions.

Example:
%LET A=%EVAL(10+5);

%LET B=%SYSEVALF(10.5+ 5.5);

%Put The value of A is &A and the value of B is &B;

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.