topic Re: Fill year months between two dates in SAS Programming

Fill year months between two dates

Sujithpeta — Wed, 22 Jan 2020 00:33:59 GMT

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.

Re: Fill year months between two dates

hashman — Wed, 22 Jan 2020 00:59:58 GMT

@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.

Re: Fill year months between two dates

Sujithpeta — Wed, 22 Jan 2020 02:07:39 GMT

@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

Re: Fill year months between two dates

hashman — Wed, 22 Jan 2020 02:48:50 GMT

@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.

Re: Fill year months between two dates

Satish_Parida — Wed, 22 Jan 2020 10:32:41 GMT

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;

Re: Fill year months between two dates

Satish_Parida — Wed, 22 Jan 2020 10:33:20 GMT

Please let us know if it worked for you.

Re: Fill year months between two dates

yabwon — Wed, 22 Jan 2020 13:14:20 GMT

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

Re: Fill year months between two dates

Sujithpeta — Wed, 22 Jan 2020 13:58:23 GMT

Thanks @hashman
It worked.

Re: Fill year months between two dates

Sujithpeta — Wed, 22 Jan 2020 13:59:43 GMT

@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