Flag record when same event occurs within defined time period

Accepted Solution Solved
Reply
Contributor SM1
Contributor
Posts: 30
Accepted Solution

Flag record when same event occurs within defined time period

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.


Accepted Solutions
Solution
‎01-27-2017 05:34 PM
Esteemed Advisor
Posts: 7,295

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

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

 

View solution in original post


All Replies
Grand Advisor
Posts: 17,383

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

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;

 

Solution
‎01-27-2017 05:34 PM
Esteemed Advisor
Posts: 7,295

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

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

 

Contributor SM1
Contributor
Posts: 30

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

Worked perfectly!

 

Thank you so much!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 138 views
  • 0 likes
  • 3 in conversation