base SAS certification sample question

[MarkWik]

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)

[art297]

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?

[MarkWik]

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.:)

[art297]

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.

[MarkWik]

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

i.

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