topic Re: Flag record when same event occurs within defined time period in SAS Programming

Flag record when same event occurs within defined time period

SM1 — Thu, 26 Jan 2017 23:56:41 GMT

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.

Re: Flag record when same event occurs within defined time period

Reeza — Fri, 27 Jan 2017 00:52:48 GMT

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;

Re: Flag record when same event occurs within defined time period

art297 — Fri, 27 Jan 2017 03:24:28 GMT

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

Re: Flag record when same event occurs within defined time period

SM1 — Fri, 27 Jan 2017 22:35:06 GMT

Worked perfectly!

Thank you so much!