BookmarkSubscribeRSS Feed
jenim514
Pyrite | Level 9

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;


1 REPLY 1
Pamela_JSRCC
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 715 views
  • 0 likes
  • 2 in conversation