topic base SAS certification sample question in SAS Programming

base SAS certification sample question

MarkWik — Sat, 06 Oct 2012 10:55:56 GMT

Hey Folks, Mark a brit living in singapore new to sas and preparing for my base SAS certfication exam scheduled next month. Care to help me with a base sas for my question?

I have a sample table with the following columns:

EMPLOYEEID DATE(mmddyy) DAILY_WAGES

E235888 07/22/2011 200

E797890 07/22/2011 180

E098098 09/23/2011 230

E353454 05/24/2011 290

E809890 02/09/2011 230

E879890 06/08/2011 240

E879890 05/30/2011 150

E879890 09/25/2011 100

i. Maximum(highest) wage of employees earned in the last 2 quarters- list by each quarter (a year has 4 quarters with 3 months in a quarter)

ii. Maximum(highest) wage earned by employees in the last one year for those employees who have been with the company for over a year?

iii. last wage (final or most recent wage earned of all employees)

Re: base SAS certification sample question

art297 — Sat, 06 Oct 2012 14:12:10 GMT

Whether one uses SAS, or any other language, the questions are not precise.

e.g., question i: is that asking for each employee, or overall. And, when does the year begin? And, if it is for each employee, is it the last two quarters in which they worked or the last two quarters of the year?

question ii: again, is that for each employee, or overall. And, given the data provided, one doesn't have a way of knowing who has been with the company for over a year

question iii: again, is that for each employee, or overall. And is that asking for individual wages or a sum?

Re: base SAS certification sample question

MarkWik — Sat, 06 Oct 2012 14:35:24 GMT

Hi Sir,

Sorry if i didn't explain well enough in the first place. Please accept my sincere apologies.

q1. Highest wage for each employee and for year period june2011 to june2012 one year period. But of course it is in dates because it's daily wages.

Yes, its the last two quarters they worked with the company because some may start in the middle of the year and quit at any random time. In other words, some may last for few days, some may last for few weeks, and some may have lasted for the whole year./* will a lag or intnx be suitable?*/

q2. It is for each employee who has been with the company for a year or more. /* i suppose the logic to use here is categorise based on periods maybe a maxdate - mindate>=365 days? will this work?*/ and then filter accordingly?

q3. It is just the last wage earned by each employee- individual's last wage . I guess this is the easiest:

proc sort data= have;

by employeeid date;

run;

data want;

set have;

by employeeid;

if last.employeeid then output;

run;/* so this will give the last record of each emp id with their salary right?

am i correct in q3? if so your authentication will give a smile for a beginner like me.:)

Re: base SAS certification sample question

art297 — Sat, 06 Oct 2012 15:14:59 GMT

I'll agree with your q3 solution, but don't think you need the "then output" as that is already inherent in the iteration.

Q1: you can identify the max date for an employee, and the year and quarter that represents. You can then use the intnx function to identify the previous quarter. Then you would only have to select the max wage for dates within those boundaries. I asked about when the year begins because, for irregular fiscal years, you may have to define the quarters using the intervalds system option.

Q2: If they data actually show each employee's start date, you can identify that by getting the min date and the most recent date with the max function and, if you are on at least 9.3, can use the yrdif function with the age option to identify how many years they have been with the firm (see: http://support.sas.com/kb/36/977.html). Your proposed solution doesn't account for leap years.

Re: base SAS certification sample question

MarkWik — Sun, 07 Oct 2012 16:00:32 GMT

Please correct my code(my answers) preferably in datastep. Thanks.

Proc sql;

create table as want as

select employeeid, max(daliy_wages) as max_wage

from have

where months_between (max(date),date)<=3

group by employeeid;

quit

ii.

Proc sql;

create table want as

select employeeid, max(daily_wages) as max_wage

from have

where months_between (max(date), date)<=12

and employeeid in (select distinct employeeid

from have

where months_between(max(date),date)>12)

group by employeeid

iii.

Proc sql;

create table want as

select employeeid, daily_wages

from have

a inner join (select employeeid, max(date)

from have b

on (a.employeeid=b.employeeid and a.date=b.date);

quit;

Thanks,

Mark