some SAS written interview questions

These are the i have been asked in writen test for one interview .I thought I should share..

Q1. Create a variable called “Flag” which indicates whether a student’s score increased or decreased from the previous record in the data. Mark a “0” for records where the student’s score was lower than the previous record. Conversely, mark a “1” for records where a student’s score was equal or higher than the previous record. Mark a “0” for the first record of each student

Output:

Obs    Studentid    year    score    flag

1        A         91      400       0

2        A         92      398       0

3        A         92      399       1

4        B         91      430       0

5        B         92      432       1

6        B         93      444       1

7        B         94      446       1

8        C         91      455       0

9        C         92      423       0

10        C         93      411       0

11        C         94      415       1

12        C         95      427       1

13        C         95      418       0

Program for question 1:

data studentscore ;

input Studentid $ year score;

cards;

A 91 400

A 92 398

A 92 399

B 91 430

B 92 432

B 93 444

B 94 446

C 91 455

C 92 423

C 93 411

C 94 415

C 95 427

C 95 418

;

run;

data new;

set studentscore;

by studentid;

first=first.studentid;

last=last.studentid;

x=lag(score);

if first=1 then y = -1; else y=score-x;

if y>=0 then flag=1; else flag=0;

run;

proc print ;

var studentid year score flag;

title 'flags for Question one';

run;

Q2. Write code to create a SAS dataset named “StudentScoreNew” by picking each student’s most recent score. If there are multiple scores for the most recent year, then pick the highest one for that year.
 Solution

Output:

Recent_     Max_

Obs    Studentid      year     score

1         A           92       399

2         B           94       446

3         C           95       427

Program:

PROC SQL ;

create table studentscorenew as

select  unique studentid, year as Recent_year, max(score)as Max_score

from studentscore where year

in (select max(year) from studentscore  group by studentid)

group by studentid

having year = max(year);

quit;

proc print ;

title 'most recent maximum score ( Question two)';

run;

Q3. Tag on the following variables to “StudentScoreNew” from the above question.

1. HighScore: the highest score of each student. (Use “StudentScore” as the input)
2.  AvgScore: Average score for each student. (Use “StudentScore” as the input)
Solution
Output:

                                          N

                           Studentid    Obs            Mean         Maximum

                           ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

                           A              3           399.0           400.0

                           B              4           438.0           446.0

                           C              6           424.8           455.0

                           ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Program:
proc means data = studentscore mean max maxdec=1;
class studentid;
var score;
output out=question_3 ;
run;

Q4. Complete the following macro.
              %macro processyear(Inputds=StudentScore, Year=91,Outputds=Year_out);
              %mend;

Solution:

We have used Proc sql to design macro &outputds refer to table name , &year refers to numeric number of year and &Outputds refers to saving table by that particular name.

Output

Student

High

Obs    Studentid    year    AVGSCORE     Score

1         C         91      428.333      455

Macro Program:

……………

%macro processyear(Inputds=, Year=,Outputds=);

PROC SQL ;

create table &Outputds as select studentid,year,avg(score)AS AVGSCORE ,max(score) AS StudentHighScore

from &Inputds where year=&year having score=max(score);

quit;

proc print;

run;

%Mend processyear;

……………………….

Call this Macro:

………….

%processyear(Inputds=StudentScore, Year=91,Outputds=Year_out);

……………..

Q4. Complete the following macro.
              %macro processyear(Inputds=StudentScore, Year=91,Outputds=Year_out);
              %mend;

Year _out should have the following variables:
Year- Year specified in the input.
StudentHighScore- “StudentID “of the student with the highest score for that year.
YearAvg- Average for the input year.

Q5. Briefly describe what the following macro does:

%macro Glue(InputFile=,InputSheet=);

PROC IMPORT OUT= WORK.Master

DATAFILE= "&inputfile."

            DBMS=EXCEL REPLACE;

            SHEET="&inputsheet";

            GETNAMES=YES;

RUN;

Proc Sort data=master;

            by order;

run;

Data _null_;

set master;

by order;

call symput('Program'||'_'||strip(put(_N_,8.)),Program);

call symput('Type_of_prog'||'_'||strip(put(_N_,8.)),Type);

call symput('Inputlist'||'_'||strip(put(_N_,8.)),InputList);

call symput ('Location'|| '_'||strip(put(_N_, 8.)),Location);

call symput ('number_of_progs',_N_);

run;

    %local a;

    %do a= 1 %to &number_of_progs. ;

        %include "&&Location_&a." ;

        %if %upcase(&&Type_of_prog_&a.) eq MACRO %then %do;

            %let program=&&Program_&a.;

            %let inputlist=(&&Inputlist_&a);

            %str(%&program. &inputlist.);

        %end;

    %end ;

%mend;

Sample excel sheet for the above program:

Order Type Program InputList Location 4 Sas Initial C:\Initial.sas 5 Macro Conversion agg_file=&agg_file, inputds=otela_data C:\conversion.sas 6 Sas Comprehension C:\comprehension.sas 7 Macro suppressioncounts Level=district,  LevelVar=g_district_irn C:\suppressioncounts.sas

Solution;

Briefly,In this program there is a macro named Glue which reads the specified worksheet saved in computer  and identifies the rows which have type MACRO and include that file specified in worksheet like conversion.sas and  compressioncounts.sas to run the macro specified at that location..

This program can be used to call multiple macros in single programs by specifying location of worksheet .in this worksheet there are locations of  macros .