40 must know Questions on Base SAS for Analysts (Skill test Solution)

data AV;
    merge Employee Salary;
    by name;
    totsal + salary;
run;

A) 3

B) 4

C) 5

Solution: (B)

If we are using any variable name within data step program it will automatically get created in output data set. Here, Three unique variables in both the tables are “name”, “age”, “salary” and one more variable created within dataset “totsal”.

data AV;
    merge employee (in=ine) salary(in=ins);
    by name;
run;

A) 4

B) 2

C) 1

D) 6

Solution: (D)

Above you look at input data sets, there is a one-to-many relationship between Employee and Salary. To know more about merging in SAS, click here.

data AV;
    merge employee (in=ine) salary(in=ins);
    by name;
    if ins=0;
run;

A) 4

B) 2

C) 1

D) 6

Solution: (B)

Here, we are talking about “in” variables and look at the below table to understand the value of in variables:

In this program, we are looking for observations where ins = 0 which means that “name” values not available in table “Salary”. In above table, you can see that only two records satisfy that criteria.

A.

Data Salary (Rename (Salary = Compensation));
     Set Salary;
run;

B.

Data Salary (Rename = (Salary = Compensation));
     Set Salary;
run;

C.

Data Salary (Rename = (Salary == Compensation));
     Set Salary;
run;

D. None of the above

Solution: (B)

Syntax to rename variable(s) in SAS is:

RENAME = (Old_Var1 = NewVar1 Old_Var2=New_Var2 Old_Var3=New_Var3 …)

data AV;
     do year=2000 to 2004;
         Capital+5000;
         capital+(capital*.10);
         output; 
     end; 
run;

1. The OUTPUT statement writes current values to the dataset immediately
2. In this case, last value for Year in the new data set is 2005
3. The OUTPUT statement overrides the automatic output at the end of the DATA step
4. The DO loop performs 5 iterations

Solution: (B)

In above program, we are writing to output dataset before END statement which means it will not write last value 2005 to output dataset so last value would be 2004. If we remove OUTPUT statement, last value would be 2005.

1. DROP
2. KEEP
3. RETAIN
4. VAR
5. Both A or B
6. Both A, B or C

Solution: (E)

Both DROP and KEEP can be used to  limit the variables in the dataset.

• The DROP= option tells SAS which variables you want to drop. If you place the DROP= option on the SET statement, SAS drops the specified variables when it reads the input data set and if you place the DROP= option on the DATA statement, SAS drops the specified variables when it writes to the output data set.
• The KEEP= option tells SAS which variables you want to keep. If you place the KEEP= option on the SET statement, SAS keeps the listed variables when it reads the input data set. On the other hand, if you place the KEEP= option on the DATA statement, SAS keeps the specified variables when it writes to the output data set.

1. DATA and SET
2. DATA, SET and INFILE
3. DATA, SET and INPUT
4. DATA, INFILE and INPUT

Solution: (D)

SET can not be used to read raw data files. SET is used to read data from one or more SAS dataset.

B.

C.

D. None of the above

Solution: (C)

INFILE statement start reading a file from first line of CSV and it can be header row also so we need to mention start row explicitly.

data WORK.EMP;
     infile'C:\AV\Skilltest\Emp.csv'dlm=','  ;
     input
          Employee_id $
          Gender $
          Name $
          DOB
          Location $
          Salary
          Manager_Emp_ID;
run;

data WORK.EMP;
     infile'C:\AV\Skilltest\Emp.csv'dlm=',' DSD firstobs =3;
     input
          Employee_id $
          Gender $
          Name $
          DOB
          Location $
          Salary
          Manager_Emp_ID;
run;

proc print data = emp;
     where Name like '_R%';
run;

Q16) Which of the following statement will calculate the age of each employee as on 05-Feb-2017?

data emp;
     set emp;
     Age = yrdif(DOB,'05Feb2017'd,'Actual');
run;

data emp;
     set emp;
     Age = yrdif(DOB,'05Feb2017','Actual');
run;

data emp;
     set emp;
     Age = yrdiff(DOB,'05Feb2017','Actual');
run;

Q17) If you submit the following program on above data set, which variables appear in table “Emp”?

data emp(drop=Manager_EMP_ID Salary);
     set emp (keep=Manager_EMP_ID Employee_ID Salary);
     if Manager_Emp_ID=101 and Salary >45000;
     Age = yrdif(DOB,'05Feb2017'd,'Actual'd);
run;

data WORK.class;
     infile'C:\AV\Skilltest\ClassScore.csv'dlm=','firstobs=2;
     input
          Name $
          Gender $
          Location $
          English
          Maths
          Hindi
          Sanskrit;
run;

Q18) In the above output, you can see following issues:

• Marks are not under right column heading like John marks in”Sanskrit” has shifted to “Maths”
• The total number of observation is 7 only.

 Which of the following command can be used with “infile” statement to remove these errors?

Q19) Which of the following command will find the number of missing marks in all variables of table “Class”.

proc means data=class N;
run;

proc means data=class N NMISS;
run;

proc means data=class SUM N;
run;

Q20) Which of the following command will help to impute the missing value of column “Hindi” with average marks of “Hindi”?

Proc SQL;
     Create table temp as Select *, mean(Hindi)  as avg_score from Class;
quit;

Data class (drop= Hindi avg_score Rename=(Hindi_2=Hindi));
     Set temp;
     If Hindi=. Then Hindi_2=avg_score;
     Else Hindi_2=Hindi;
run;

Proc SQL;
     Create table temp as Select *, mean(Hindi)  asavg_score from Class;
quit;

Data class (drop= Hindi avg_score Rename=(Hindi_2=Hindi));
     Set class;
     If Hindi=. Then Hindi_2=avg_score;
     Else Hindi_2=Hindi;
run;

Q21) Which of the following statements can be used to append the Table-1 and Table-2 having a unique value of Product_ID?

data work.merge_table NODUPKEY;
     set table1 table2;
run;

data work.merge_table;
     set table1 table2;
run;

PROC SORT DATA = merge_table OUT = merge_table NODUPKEY;
     by Product_ID;
run;

data work.merge_table;
     set table1 table2 nodupkey;
run;

PROC SORT DATA = merge_table OUT = merge_table;
     by Product_ID ;
run;

Q22) With cash crunch (due to demonetization) the company decided to advance the proposed booking date by 2 months (keeping the day intact). Which of the below SAS formula can be used to advance the date?

A.

data work.av_date;
     set work.merge_table;
     proposed_booking_date1=put(intnx('month',proposed_booking_date,day),date9.);
run;

B.

data work.av_date;
     set work.merge_table;
     proposed_booking_date1=put(intnx('month',proposed_booking_date,2,'s'),date9.);
run;

C.

data work.av_date;
     set work.merge_table;
     proposed_booking_date1=put(intnx('month',proposed_booking_date,sameday),date9.);
run;

D.

data work.av_date;
     set work.merge_table;
     proposed_booking_date1=put(intnx('month',proposed_booking_date,1),date9.);
run;

Solution: (B)

Look, at the syntax of INTNX() function:

INTNX ( interval, from, n < , alignment > ) ;

The arguments to the INTNX function are as follows:

interval: is a character constant or variable that contains an interval name

from: is a SAS date value (for date intervals) or datetime value (for datetime intervals)

n: is the number of intervals to increment from the interval that contains the from value

alignment: controls the alignment of SAS dates, within the interval, used to identify output observations. Allowed values are BEGINNING, MIDDLE, END, and SAMEDAY/S.

Q23) If the following code will run, what will be the output?

data table_A (Drop = Location);
      merge table1(in=Proposed_Booking_Date) table3(in=Date);
      if Proposed_Booking_Date;
      if Date then Discount_INR=Discount_Dollar*Dollar_Rate;
run;

A.

B.

C.

Solution: (C)

“IN” variable does not appear in output dataset. Here, “Proposed_Booking_Date” and “Date” are “IN” variables and we have dropped the variable “Location” in data step.

data t2;
     set TABLE2;
     if Location="Delhi" then Location="Delhi_NCR";
run;

B.

data t2;
     format location $10.;
     set TABLE2;
     if Location="Delhi" then Location="Delhi_NCR";
run;

C.

data t2;
     length Location $10;
     format location $10.;
     set TABLE2;
     if Location="Delhi" then Location="Delhi_NCR";
run;

D. Both B and C

E. Both A and B

F. None of the above

Solution: (D)

The length of field “Location” in table2 is 8 so first we need to change the format of “Location”. Here in both options B and C, we have changed the length of field “Location”.

A.  True

B.  False

Solution: (A)

Yes, it is possible. In case of one unique value for BY variable then this record is the first and last record as well.

A.  @n

B.  +N

C.  /

D.  All of the above

Solution: (C)

You can use one or more forward slash (/) line pointer controls in your INPUT statements to tell SAS to advance to a new record before reading the next data value.

 A.

proc freq data=Table5;
     tables Education;
run;

B.

proc means data=Table5;
     var Education;
run;

C. Both A and B

D.  None of the above

Solution: (A)

Proc Means is used to look at the frequency distribution of categories of a categorical variable whereas PROC Means used to explore continuous variables.

data Table5;
     set Table5;
     Salutation = scan(name, 1);
run;

B.

data Table5;
     set Table5;
     Salutation = scan(name, -1);
run;

C.

data Table5;
     set Table5;
     Salutation = scan(name, 0);
run;

D

data test2;
     set Table5;
     Salutation = scan(name, “ ”,1);
run;

E

data test2;
     set Table5;
     Salutation = scan(name, “.”,1);
run;

Solution: (A)

Below is the syntax of function SCAN:

SCAN(string, count_words)

String: A constant string or variable have a string value

Count: is a nonzero numeric constant, variable, or expression that has an integer value that specifies the number of the word in the character string that you want SCAN to select

• If count is positive, SCAN counts words from left to right in the character string.
• If count is negative, SCAN counts words from right to left in the character string.

In above question, we need to extract the first word of string so value of count would be 1 and string variable is “name”.

data AV;
     Set table5;
     if Loan_Status = "Y" then Loan_Status_Y = 1; else Loan_Status_Y = 0;
     if Loan_Status = "N" then Loan_Status_N = 1; else Loan_Status_N = 0;
     if Loan_Status = "H" then Loan_Status_H = 1; else Loan_Status_H = 0;
run;

B.

data AV;
     Set table5;
     Loan_Status_Y= (Loan_Status = "Y");
     Loan_Status_N= (Loan_Status = "N");
     Loan_Status_H= (Loan_Status = "H");
run;

C.  Both A and B

D.  None of the above

Solution: (D)

First of all, here we are creating dummy variables for variable “Loan_Status” (also known as One Hot Encoding). Both Option A and B will create these dummy variables but after execution of both program you will not be able to create exactly similar dataset like AV because it will have more number of variables and the values of dummy variables for “Loan_Status_H” and “Loan_Status_N” is swapped in output table “AV”.

Proc Freq data=table5;
     tables Education*Loan_Status;
run;

B.

Proc Freq data=table5; ?
     tables Education Loan_Status;
run;

C.

Proc Univariate data = table5;
     var Education Loan_Status;
run;

D.

Proc Univariate data = table5;
     var Education*Loan_Status;
run;

Solution: (A)

Above, we are trying to create a two-way table based on two categorical variables “Education” and “Loan_Status”. And to create two-way table, we need to place * in between them. If we will separate the variable name by space then this will create two individual frequency distributions for both the variables.

B.   False

Solution: (B)

In the first program, we have “LoanAmount” in input data set so there would be values 0.4*LoanAmount in “Charge” column whereas, in the second program, we have dropped the variable “LoanAmount” so the value of column “Charge” would be missing because we do not have variable “LoanAmount”.

B. RETAIN

C. UPDATE

D. SUM

Solution: (B)

The RETAIN statement simply copies retaining values by telling the SAS not to reset the variables at the beginning of each iteration of the DATA step. If you would not use retain statement then SAS would reset the variable at the beginning of each iteration

B. Change the ** in the BMI formula to a single *

C.   Change bmi to BMI in the WHERE statement

Solution: (A)

We can not apply WHERE on derived or calculated variable(s) so we should use IF for subsetting.

data transposed; 
     set base;
     array Qtr{3} Q:;
     do i = 1 to 3;
         Period = cat('Qtr',i);
         Amount = Qtr{i};
         output;
     end;
     drop Q1 Q2 Q3 i;
     if Amount ne .;
run;

B.

proc transpose data = base out = transposed
               (rename=(Col1=Amount) where=(Amount ne .)) name=Period;
     by cust; 
run;

C.       Both A and B

D.     None of the above

Solution: (C)

Both program can be used to transpose the data set, One is array approach whereas in second method, we are using PROC Transpose.

B) False

Solution: (B)

One of the scenarios, we have discussed in question 35.

B. PROC UNIVARIATE

C. PROC PLOT

D. None of the above

Solution: (A)

PROC SGPLOT can be used to create all above-mentioned charts.

PROC SGPLOT DATA = Table6;
     SERIES X = Month Y = Product1; 
     SERIES X = Month Y = Product2;
     SERIES X = Month Y = Product3;
run;

B.

PROC SGPLOT DATA = Table6;
     by Month;
     Var Product1 Product2 Product3; 
run;

C.

PROC SGPLOT DATA = Table6;
     Line Month;
     Var Product1 Product2 Product3;
run;

D. None of the above

Solution: (A)

Above, we are creating three series of line in a single chart and we don’t have any “Line” and “BY” statements in PROC SGPLOT.

proc plot data = Table6;
     scatter X=Product1 Y=Product2 size= Product3 
     /fillattrs=(color = teal) datalabel = Month;
run;

B.

proc sgplot data = Table6;
     bubble X=Product1 Y=Product2 size= Product3
     /fillattrs=(color = teal) datalabel = Month;
run;

C.

proc sgplot data = Table6;
     scatter X=Product1 Y=Product2 size= Product3
     /fillattrs=(color = teal) datalabel = Month;
run;

D.

proc chart data = Table6;
     scatter X=Product1 Y=Product2 size= Product3
     /fillattrs=(color = teal) datalabel = Month;
run;

Solution: (B)

In bubble chart, we have three variables to visualize. One on x-axis, second one on y-axis and last one as size of bubble. We can create Bubble chart in SAS using PROC SGPLOT with Bubble statement.

Proc Sort data=table7; 
     by ID Area_Type Descending Volume;
run;
Proc SORT Data=table7 out=table8 nodupkey;
     by ID Area_Type; 
run;

B.

Proc Sort data=table7; 
     by ID Area_TypeVolume Descending;
run;
Proc SORT Data=table7 out=table8 nodupkey;
     by ID Area_Type;
run;

C.

Proc SORT Data=table7 out=table8 nodupkey; 
     by ID Area_TYPE Volume Descending;
run;

D. Both B and C

Solution: (A)

The basic problem with Option B and C is, “Descending” option is appearing after the variable name which is not the right syntax. In option A, we are first sorting the data set based on “ID”, “Area_Type” and “Volume” (Descending) then again writing a PROC SORT to remove duplicate records based on “ID” and “Area_Type”.

Data table7;
     set table7;
     select (Volume);
            when (le 30)     Volume_Bucket="A";
            when (le 60)     Volume_Bucket="B";
            otherwise        Volume_Bucket="C";
     end;
run;

B.

Data table7;
     set table7;
     if Volume < 30 then Volume_Bucket ="A";
     Else if Volume <60 then Volume_Bucket="B";
     Else Volume_Bucket="C";
run;

C. Both A and B

D. None of the above

Solution: (B)

Select statement works with exact value, it does not compare like greater than or less than so here IF statement will do the task.