Flag record when same event occurs within defined time period

[SM1]

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.

[Reeza]

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;

[art297 · Accepted solution]

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

[SM1]

Worked perfectly!

Thank you so much!