Hello,
I am working with personnel records that contain information about events (accession, promotion, retirement).
I have the data both in long and wide (after transposing) format.
Accession events are codes 101 to 105
Promotion events are codes 400 to 600
Retirement events are codes 800 to 999
In the wide format, the data looks like this:
ID EventDate1 EventCode1 EventDate2 EventCode2 EventDate3 EventCode3 EventDate4 EventCode4
1 05/01/1980 101 08/13/1985 501 03/17/1987 801
2 10/26/1996 105 11/16/1996 105 01/18/2001 402 03/24/2008 801
3 11/04/1991 103 05/13/2004 575 09/24/2015 825 09/25/2015 826
I need to flag a record where 2 consecutive events are the same type if the 2 events occur within 30 days of each other. So I would like to generate 3 new variables:
FlagA - flag for 2 accession events that happen within 30 days of each other
FlagP - flag for 2 promotion events that happen within 30 days of each other
FlagR - flag for 2 retirement events that happen within 30 days of each other
Output would look like this:
ID EventDate1 EventCode1 EventDate2 EventCode2 EventDate3 EventCode3 EventDate4 EventCode4 FlagA FlagP FlagR
1 05/01/1980 101 08/13/1985 501 03/17/1987 801 0 0 0
2 10/26/1996 105 11/16/1996 105 01/18/2001 402 03/24/2008 801 1 0 0
3 11/04/1991 103 05/13/2004 575 09/24/2015 825 09/25/2015 826 0 0 1
Any suggestions will be most appreciated.
This is just an extension/completion of @Reeza's code:
data want; set have; array edates(*) eventdate:; array ecodes(*) eventcode:; flagA=0; flagP=0; flagR=0; do _n_=1 to dim (ecodes)-1; if substr(ecodes(_n_+1),1,1) eq substr(ecodes(_n_),1,1) and edates(_n_+1)-edates(_n_) le 30 then do; select; when (ecodes(_n_) in (100:105)) flagA=1; when (ecodes(_n_) in (400:600)) flagP=1; when (ecodes(_n_) in (800:999)) flagR=1; otherwise; end; end; end; run; ;
HTH,
Art, CEO, AnalystFinder.com
Can you assume any order in the variables? Do you know how many dates/codes you need to filter through in advance?
You can use a DO loop, possibly a nested DO loop if you can't assume any order, in combination with two arrays for types and dates.
I would check for the 30 day interval first and then check if the events are the same.
Here's a code sketch - note this is untested and may have syntax errors, but should give you a good idea.
data want;
set have;
array edates(*) eventdate1-eventdate4;
array ecodes(*) eventcode1-eventcode4;
flagA=0; flagP=0; flagR=0;
do i=1 to dim (edates)-1;
if 0<=e_dates(i+1)-edates <=30 then do;
if substrn(e_codes(i), 1,1) in (100:105) and substrn(e_codes(i+1),1,1) in (100:105) then flagA=1;
end;
end;
run;
This is just an extension/completion of @Reeza's code:
data want; set have; array edates(*) eventdate:; array ecodes(*) eventcode:; flagA=0; flagP=0; flagR=0; do _n_=1 to dim (ecodes)-1; if substr(ecodes(_n_+1),1,1) eq substr(ecodes(_n_),1,1) and edates(_n_+1)-edates(_n_) le 30 then do; select; when (ecodes(_n_) in (100:105)) flagA=1; when (ecodes(_n_) in (400:600)) flagP=1; when (ecodes(_n_) in (800:999)) flagR=1; otherwise; end; end; end; run; ;
HTH,
Art, CEO, AnalystFinder.com
Worked perfectly!
Thank you so much!
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: