10-06-2012 06:55 AM
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)
10-06-2012 10:12 AM
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?
10-06-2012 10:35 AM
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;
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.
10-06-2012 11:14 AM
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.
10-07-2012 12:00 PM
Please correct my code(my answers) preferably in datastep. Thanks.
create table as want as
select employeeid, max(daliy_wages) as max_wage
where months_between (max(date),date)<=3
group by employeeid;
create table want as
select employeeid, max(daily_wages) as max_wage
where months_between (max(date), date)<=12
and employeeid in (select distinct employeeid
group by employeeid
create table want as
select employeeid, daily_wages
a inner join (select employeeid, max(date)
from have b
on (a.employeeid=b.employeeid and a.date=b.date);