04-14-2016 02:21 PM
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;
04-14-2016 02:53 PM
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.
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);