Hello All,
I have a dataset with 3 observations, each has an index date. I would like to create a date series for every 6 months starting from the index date until 31 Dec 2021. Here is the example.
Original dataset
id indexdate
1 01JAN2021
2 10MAR2021
3 15NOV2021
Final dataset
id indexdate date1 date2 date3
1 01JAN2021 01APR2021 01JUL2021 01OCT2021
2 10MAR201 10JUN2021 10SEP2021 .
3 15NOV2021 . . .
Many thanks!
Since you can't really know how many dates for a specific id you will have, you will create a long dataset:
data have;
input id $ indexdate :date9.;
format indexdate yymmdd10.;
datalines;
1 01JAN2021
2 10MAR2021
3 15NOV2021
;
data want;
set have;
format qdate yymmdd10.;
qdate = intnx('quarter',indexdate,1,'s');
do while (qdate le '31dec2021'd);
output;
qdate = intnx('quarter',qdate,1,'s');
end;
keep id qdate;
run;
Sorry, it should be every 3 months. Not every 6 months.
Since you can't really know how many dates for a specific id you will have, you will create a long dataset:
data have;
input id $ indexdate :date9.;
format indexdate yymmdd10.;
datalines;
1 01JAN2021
2 10MAR2021
3 15NOV2021
;
data want;
set have;
format qdate yymmdd10.;
qdate = intnx('quarter',indexdate,1,'s');
do while (qdate le '31dec2021'd);
output;
qdate = intnx('quarter',qdate,1,'s');
end;
keep id qdate;
run;
data want;
set have;
array date(*) date1-date6;
_date = indexdate;
do i=1 to 6 (while _date <= '31Dec2021'd);
_data = intnx('month', indexdate, i, 'b');
data(i) = _data;
end;
run;
You may need to tweak that DO loop or add an IF condition but it should get you started.
This one should work for sure but I feel like it could be simplified.
data want;
set have;
array date(*) date1-date6;
do i=1 to 6 ;
_data = intnx('month', indexdate, i, 'b');
if _data <= '31Dec2021'd then data(i) = _data;
end;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.