Date:         Sat, 18 May 2002 09:39:04 -0500
Reply-To:     "Gallegly, Tony" <Tony.Gallegly@TRICON-YUM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Gallegly, Tony" <Tony.Gallegly@TRICON-YUM.COM>
Subject:      Please Help with Efficient Alternative Code
Content-Type: multipart/alternative;

I need to generate a list of individuals that were employed during
different time periods.  I have two tables that contain the necessary
information.

1.  Employee Work History:  The work history table stores changes to an
employees work status.  It can be assumed that an employee is employed
as long as the most recent row is a row that can be associated with
active employment.
For Example:
Name            Effective Date          Action          Cost Center
John S          02/19/2000              Hire            123
John S          06/25/2000              Transfer        345
John S          11/05/2000              Promotion       456
John S          01/15/2001              Termination     456     	
 
2.  Calendar table:  The time periods are consecutive 28 day intervals.
For Example
Period          Beginning Date  End Date
1               01/01/2000              01/29/2000
2               01/30/2000              02/27/2000
3               02/28/2000              02/27/2000

John Smith would be counted as an active employee from 2/19/2000 to
1/15/2001 so his name would appear in each period.

I am currently creating a separate data set for each time period keeping
only employee's whose max effective date is prior to the end date of the
period and the action associated with that effective date is considered
an action for an active employee.  The code below is crude and very CPU
intensive since I have over 30 periods.  Can you please advise on a more
efficient way to program this.

DATA P1 P2 P3 P4;
SET JOB;                                         
     IF EFFDATE < MDY(01,20,2000) THEN OUTPUT P1;
     IF EFFDATE < MDY(02,17,2000) THEN OUTPUT P2;
     IF EFFDATE < MDY(03,16,2000) THEN OUTPUT P3;
     IF EFFDATE < MDY(04,13,2000) THEN OUTPUT P4;

PROC SORT DATA = P1; BY EMPLID DESCENDING EFFDATE;
PROC SORT DATA = P2; BY EMPLID DESCENDING EFFDATE;
PROC SORT DATA = P3; BY EMPLID DESCENDING EFFDATE;
PROC SORT DATA = P4; BY EMPLID DESCENDING EFFDATE;

DATA P1; SET P1; BY EMPLID; IF FIRST.EMPLID;
PERIOD = '200001';                          
DATA P2; SET P2; BY EMPLID; IF FIRST.EMPLID;
PERIOD = '200002';                          
DATA P3; SET P3; BY EMPLID; IF FIRST.EMPLID;
PERIOD = '200003';                          
DATA P4; SET P4; BY EMPLID; IF FIRST.EMPLID;
PERIOD = '200004';                          
    
Thanks
Tony Gallegly



This communication is confidential and may be legally privileged.  If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system.  Failure to follow this process may be unlawful.  Thank you for your cooperation.

[text/html]