topic Re: How to add months between two dates? in SAS Programming

How to add months between two dates?

raajdesaii — Tue, 29 Dec 2020 17:48:20 GMT

Hello all,

How do I create a new variable for each month between two dates?

ID	Startdate	Enddate
1	01JAN2016	06JUN2016

What I want is:

ID	Startdate	Enddate	Newdate
1	01JAN2016	06JUN2016	JAN2016
1	01JAN2016	06JUN2016	FEB2016
1	01JAN2016	06JUN2016	MAR2016
1	01JAN2016	06JUN2016	APR2016
1	01JAN2016	06JUN2016	MAY2016
1	01JAN2016	06JUN2016	JUN2016

Thanks!

Re: How to add months between two dates?

singhsahab — Tue, 29 Dec 2020 18:04:28 GMT

data want;
set have;
st_month=month(startdate);
en_month=month(Enddate);
do i=st_month-1 to en_month-1;
 new_date=cat(put(st_month+i,z2.),' ',put(year(startdate),z4.));
output;
end;
drop i st_month en_month;
run;

you have to apply a user define format to convert month number in character format.

Re: How to add months between two dates?

PhilC — Tue, 29 Dec 2020 18:50:06 GMT

I agree with @RichardDeVen and @PGStats , and recommend using the INTNX function. See our answers to your other post:

Re: How do I count unique observation by creating ... - SAS Support Communities

Re: How to add months between two dates?

novinosrin — Tue, 29 Dec 2020 20:04:10 GMT

Hi @raajdesaii Your WANT seems to suggest you want the monthly sequences between start and end in monyy7. format. I guess there's nothing more. Or perhaps, it's just a matter of ways to write a loop is the question with dates being mere integers-


data have;
input ID	(Startdate	Enddate) (:date9.);
format Startdate	Enddate date9.;
cards;
1	01JAN2016	06JUN2016
;

data want;
 set have;
 new_date=startdate;
 do while(new_date<Enddate);
  output;
  new_date=intnx('mon',new_date,1);
 end;
 format new_date monyy7.;
run;

Re: How to add months between two dates?

raajdesaii — Tue, 29 Dec 2020 22:41:21 GMT

Thank you. This worked!

Re: How to add months between two dates?

Ksharp — Wed, 30 Dec 2020 11:55:22 GMT


data have;
input ID	(Startdate	Enddate) (:date9.);
format Startdate	Enddate date9.;
cards;
1	01JAN2016	06JUN2016
;
data want;
 set have;
 do date= Startdate	to Enddate;
  if month ne month(date) then do;new_date=date;month=month(date);output;end;
 end;
 format new_date monyy.;
 drop month date;
run;

Just for some fun.