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.
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.
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.
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
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.