Fill year months between two dates

[Sujithpeta]

Hey,

Have:

PatientID     Min_YearMo    Max_YearMo

A                     201711             201803

B                     201801            201803

Want:

PatientID     Min_YearMo    Max_YearMo

A                     201711             201803

A                     201712             201803

A                     201801             201803

A                     201802             201803

B                     201801             201803

B                     201802             201803

I want create new rows based on number of month difference between min and max yearmonths. 

Max_YearMo is always constant across all patient. 

Thanks, appreciate your help.

[hashman]

@Sujithpeta:

INTNX is your friend:

data have ;                                                                                                                                      
  input patientid $ (min_yearmo max_yearmo) (:yymmn6.) ;                                                                                         
  format m: yymmd7. ;                                                                                                                            
  cards ;                                                                                                                                        
A  201711  201803                                                                                                                                
B  201801  201803                                                                                                                                
;                                                                                                                                                
                                                                                                                                                 
data want ;                                                                                                                                      
  set have ;                                                                                                                                     
  do _n_ = 1 by 1 until (min_yearmo = max_yearmo) ;                                                                                              
    output ;                                                                                                                                     
    min_yearmo = intnx ("mon", min_yearmo, 1) ;                                                                                                  
  end ;                                                                                                                                          
run ;   

Kind regards

Paul D. 

[Sujithpeta]

@hashman 

I have created min_yearmo and max_yearmo using below code, both came out with format BEST12.

min_yearmo = YEAR(fromdate)*100+month(fromdate);
max_yearmo = YEAR(end_date)*100+month(end_date);

In this scenario, to which format should I change this to apply your code? I tried the format yymmn6. but ended up converting the 2017-11-02 date to 251204. Even still, I ran the code using the above format and the code is taking forever to run so had to stop the code.

Appreciate your help. Thanks

[hashman · Accepted solution]

@Sujithpeta:

In SAS, storing dates as a YYYYMM number is highly deprecated for a variety of reasons. However, if this is what you have in your input data and if you want to keep it such on the way out, some on-the-fly transformation is needed to make them into SAS date values, or otherwise the INTNX function cannot operate properly:

data have ;                                                                                                                                      
  input patientid $ (min_yearmo max_yearmo) (:6.) ;                                                                                              
  cards ;                                                                                                                                        
A  201711  201803                                                                                                                                
B  201801  201803                                                                                                                                
;                                                                                                                                                
                                                                                                                                                 
data want (drop = _:) ;                                                                                                                          
  set have ;                                                                                                                                     
  _minym = input (put (min_yearmo, z6.), yymmn6.) ;                                                                                              
  do _n_ = 1 by 1 until (_minym = input (put (max_yearmo, z6.), yymmn6.)) ;                                                                      
    output ;                                                                                                                                     
    _minym = intnx ("mon", _minym, 1) ;                                                                                                          
    min_yearmo = input (put (_minym, yymmn6.), 6.) ;                                                                                             
  end ;                                                                                                                                          
run ;      

Kind regards

Paul D.

[Sujithpeta]

Thanks @hashman
It worked.

[Satish_Parida]

This not ideal but it works.

data want;
set have;
output;
do while (Min_YearMo<Max_YearMo);
	Min_YearMo_y=floor(Min_YearMo/100);
	Min_YearMo_m=mod(Min_YearMo,100);
	Min_YearMo_m=Min_YearMo_m+1;
	if Min_YearMo_m>12 then do;
		Min_YearMo_m=1;
		Min_YearMo_y=Min_YearMo_y+1;
	end;
	Min_YearMo=Min_YearMo_y*100+Min_YearMo_m;
	if Min_YearMo<Max_YearMo then output;
end;
drop Min_YearMo_y Min_YearMo_m;
run;

[Satish_Parida]

Please let us know if it worked for you.

[yabwon]

Hi @Sujithpeta ,

one more approach, when you have numeric values like 201901, could be:

data have ;                                                                                                                                      
  input patientid $ min_yearmo max_yearmo ;                                                                                         
  cards ;                                                                                                                                        
A  201711  201803
B  201801  201803
C  201801  201911
;                                                                                                                                                
run;
 
data want ;                                                                                                                                      
  set have ;                                                                                                                                     
  do until (min_yearmo > max_yearmo);
      output ; 
      /*put _all_;*/
      min_yearmo + 1 ;
      if (mod(min_yearmo,100)>12) then min_yearmo + 88; 
  end ;                                                                                                                                          
run ;

All the best

Bart

[Sujithpeta]

@yabwon
Thanks for the solution. Other person code worked for me yesterday. I'll try this approach as well and let you know if it worked. Thanks agian