BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SM1
Obsidian | Level 7 SM1
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
Reeza
Super User

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
Opal | Level 21

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
Obsidian | Level 7 SM1
Obsidian | Level 7

Worked perfectly!

 

Thank you so much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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