CODEWITHSUNDEEP
sas
NewUsr_stat
NewUsr_stat

Reputation: 2583

Loop over time periods

suppose to have the following data set:

ID          Date_Start    Date_End      Flag1       Flag2    
001         13JAN2015     01JUN2018       1           0
001         02JUN2018     02JUL2018       1           0
001         03JUL2018     31DEC2020       1           0           
002         01JAN2015     31DEC2020       1           0                     
003         01JAN2017     31DEC2019       1           0                    
003         01JAN2020     31DEC2021       1           0                     
004         01JAN2011     31DEC2021       1           2               
.....        .........     .........     .....      ......

Desired output:

ID          Date_Start    Date_End      Flag1       Flag2    
001         13JAN2015     01JUN2018       1           0
001         02JUN2018     02JUL2018       1           0
001         03JUL2018     31DEC2020       1           10           
002         01JAN2015     31DEC2020       1           10                     
003         01JAN2017     31DEC2019       1           0                    
003         01JAN2020     31DEC2021       1           10                     
004         01JAN2011     31DEC2021       1           2

..... ......... ......... ..... ......

In other words: if Flag2 == 0 and Flag1 == 1 replace the flag in Flag2 column with 10 for each ID as follows:

  1. for replicated IDs take the last interval of time;
  2. for unique IDs take the interval you have.

I'm a newbie in SAS programming. I know that what I have to do is:

     data my data;
       set input;
       if Flag2 = 0 AND Flag1 = 1 then Flag2 = 10
     run;

but I don't know how to manage periods and replicated IDs. Can anyone help me please?

Upvotes: 0

Views: 54

Answers (1)

PeterClemmensen
PeterClemmensen

Reputation: 4937

I'm not entirely sure here, but I think this is what you want.

data have;
input ID $ (Date_Start Date_End)(:date9.) Flag1 Flag2;
format Date_Start Date_End date9.;
datalines;
001 13JAN2015 01JUN2018 1 0 
001 02JUN2018 02JUL2018 1 0 
001 03JUL2018 31DEC2020 1 0 
002 01JAN2015 31DEC2020 1 0 
003 01JAN2017 31DEC2019 1 0 
003 01JAN2020 31DEC2021 1 0 
004 01JAN2011 31DEC2021 1 2 
;

data want;
   set have;
   by ID;
   if last.ID and flag1 = 1 and flag2 = 0 then flag2 = 10;
run;

Result

ID   Date_Start  Date_End   Flag1  Flag2
001  13JAN2015   01JUN2018  1      0 
001  02JUN2018   02JUL2018  1      0 
001  03JUL2018   31DEC2020  1      10 
002  01JAN2015   31DEC2020  1      10 
003  01JAN2017   31DEC2019  1      0 
003  01JAN2020   31DEC2021  1      10 
004  01JAN2011   31DEC2021  1      2

Upvotes: 1

Related Questions