topic Re: Create a date series until a certain date in SAS Programming

Create a date series until a certain date

di_niu0 — Wed, 21 Apr 2021 18:09:46 GMT

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!

Re: Create a date series until a certain date

di_niu0 — Wed, 21 Apr 2021 18:11:10 GMT

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

Re: Create a date series until a certain date

Kurt_Bremser — Wed, 21 Apr 2021 18:45:14 GMT

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;

Re: Create a date series until a certain date

Reeza — Wed, 21 Apr 2021 19:08:37 GMT

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;

Re: Create a date series until a certain date

di_niu0 — Wed, 21 Apr 2021 20:39:09 GMT

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

Re: Create a date series until a certain date

di_niu0 — Wed, 21 Apr 2021 20:40:01 GMT

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

Re: Create a date series until a certain date

Reeza — Wed, 21 Apr 2021 20:47:18 GMT

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

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

Re: Create a date series until a certain date

di_niu0 — Wed, 21 Apr 2021 20:57:33 GMT

Yes. Thanks!