Assign labels based on multiple conditions

Question

suppose to have the following:

  ID     Start_date      End_date    Hospital  Work             
 00001   01JAN2015      15JAN2015      006      w   
 00001   16JAN2015      16JAN2015      006      p                       
 00001   17JAN2015      20JAN2015      006      w       
 00001   21JAN2015      29JAN2015      006      f                     
 00001   30JAN2015      02FEB2015      004      w         
 00001   03FEB2015      03FEB2015      004      s            
 00001   04FEB2015      08FEB2015      004      w       
 00001   09FEB2015      13FEB2015      004      f        
 00001   14FEB2015      16FEB2015      006      f 
 00001   17FEB2015      28DEC2016      006      w       
 00001   29DEC2016      31DEC2016      006      w 
 ....      .....         ......        ...     ...

Desired output:

  ID     Start_date      End_date    Hospital  Work  Flag1   Flag2                  
 00001   01JAN2015      15JAN2015      006      w      1       4    
 00001   16JAN2015      16JAN2015      006      p      4       9                      
 00001   17JAN2015      20JAN2015      006      w      9       4     
 00001   21JAN2015      29JAN2015      006      f      4       9                  
 00001   30JAN2015      02FEB2015      004      w      9       2    
 00001   03FEB2015      03FEB2015      004      s      2       9         
 00001   04FEB2015      08FEB2015      004      w      9       4     
 00001   09FEB2015      13FEB2015      004      f      4       9      
 00001   14FEB2015      16FEB2015      006      f      9       2      
 00001   17FEB2015      28DEC2016      006      w      2       4
 00001   29DEC2016      31DEC2016      006      w      4      Stop
 ....      .....         ......        ...     ...

in other words I need to add two columns: Flag1 and Flag2 containing indices with the following criteria: if the the first Start_date for the ID then Flag1 must always be 1. Then flag2 will contain four indices as follows: 4 if "w" in Work column, 9 if not "w" in Work column (f, s or other), 2 if Hospital changes (here from 006 to 004 and then 006 again) and Stop for the end of the period, here 31DEC2016 but it could be 31DEC2019 or 31DEC2020 depending on the ID. Totally I have 350 IDs that are repeated because I have many periods per ID. Column Flag1 will take the previous index of Flag2 column.

Can anyone help me please?Thank you in advance

crow16384 · Answer

data source_data;
    input ID :$5. Start_date :date9. End_flag :date9. Hospital  :$3. Work :$1.;
    format Start_date End_flag date9.;
    datalines;
    00001   01JAN2015      15JAN2015      006      w   
    00001   16JAN2015      16JAN2015      006      p                       
    00001   17JAN2015      20JAN2015      006      w       
    00001   21JAN2015      29JAN2015      006      f                     
    00001   30JAN2015      02FEB2015      004      w         
    00001   03FEB2015      03FEB2015      004      s            
    00001   04FEB2015      08FEB2015      004      w       
    00001   09FEB2015      13FEB2015      004      f        
    00001   14FEB2015      16FEB2015      006      f 
    00001   17FEB2015      28DEC2016      006      w       
    00001   29DEC2016      31DEC2016      006      w
;

proc sort data=source_data;
  by ID start_date hospital;
run;

data destination_data;
  retain ID Start_date End_flag Hospital Work Flag1 Flag2;
  attrib Flag1 length=$8 Flag2 length=$8;
  set source_data;
  by id start_date hospital;

  retain Flag2R;

  if work='w' then Flag2='4';
    else Flag2='9';

  if not first.ID and lag(hospital) NE hospital then Flag2='2';
  if last.ID then Flag2='Stop';  
 
  Flag2R=lag(Flag2);

  if first.ID then flag1='1';
    else flag1=Flag2R;

  drop Flag2R;
run; 

proc print data=destination_data noobs;
run;

Assign labels based on multiple conditions

1 Answers