Identify change in pay rate in personnel record

jenim514 — Thu, 14 Apr 2016 18:21:17 GMT

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;

Re: Identify change in pay rate in personnel record

Pamela_JSRCC — Thu, 14 Apr 2016 18:53:00 GMT

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);

topic Re: Identify change in pay rate in personnel record in SAS Programming

Identify change in pay rate in personnel record

Re: Identify change in pay rate in personnel record