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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: