Identify change in pay rate in personnel record

[jenim514]

Hi! I need some help with figuring out pay change from baseline.

- All records are ordered by SSN

'Events' individual observations that reflect any changes to the personnel record (including pay, marital, dependent, hire, promotion, relocation, etc...).  "Pay' is a variable that is populated at the time of each event/ observation.  

1.  Pay1 (baseline) is the pay rate at time of any event prior to 9/20/2010. ('m really only interested in the baseline pay at the time of the last event)

2.  Then I check for any events (changes to personnel record) that took place during the next 12 month interval.  If there was an event, then output Pay2 (so I can check against pay1.

3.  THis is where I am running into problems.  If there was an event during interval 2, then pay2 is populated, however I need to populate a new variable "pay_change" =Y/N to indicate if there was change between Pay 2 and Pay1.  Since pay2 is checking longitudinally to Pay1, Pay1 is always blank since Pay1 are records are populated prior to the next Interval date.  Is there a way to check against the last populated record from Pay1 (baseline)? 

Any help is mcuh apprecited!!

Here is my attempted (but failed) syntax:

Data lastevent_pay
Set . sponsor_event;
By ssn;
*Pay Grade- Baseline;
if '30SEP2010'd > =eventdate then do;
    pay1=pay_grade;
end;
If '01OCT2010'd <=eventdate <= '30SEP2011'd then do; 
      pay2=pay_grade;
end;
If pay2=last.pay1 then pay_change1= 'N';
    Else if pay2=' ' then pay_change1='N';
    Else if pay2 ^=last.pay1 then pay_change1='Y';

    run;

[Pamela_JSRCC]

I'm not sure what you are doing with the baseline date logic, so I've not addresses that.  Below is a join that will tell you if an event includes a change in pay_grade, i.e. chg_flag = 1, from the most recent previous event.

proc sql;
select  a.ssn, a.eventdate,
        b.pay_grade as prev_pay,
        a.pay_grade as curr_pay,
        case when b.pay_grade = a.pay_grade
             then 0 else 1 end format 3. as chg_flag
from      sponsor_event A
left join sponsor_event B on b.ssn       = a.ssn and
                             b.eventdate = (select max(c.eventdate) from sponsor_event C
                                            where  c.ssn        = a.ssn
                                            and    c.event_date < a.event_date);