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!
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;
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.
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
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;
Thank you. This worked!
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: