Create a date series until a certain date

[di_niu0]

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! 

[di_niu0]

Sorry, it should be every 3 months. Not every 6 months. 

[Kurt_Bremser · Accepted solution]

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;

[di_niu0]

Thank you! I need to transpose it afterwards. But I'm managed to get the right format.

[Reeza]

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; 

[di_niu0]

Thank you! I think you are doing for every month. But I could modify it to every 6 months.

[Reeza]

Oh, yeah, that should be 3...multiply i by the intervals you want, ie 3, 6 etc.

_data = intnx('month', indexdate, i*3, 'b');

[di_niu0]

Yes. Thanks!