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;
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);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.