BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
di_niu0
Obsidian | Level 7

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

7 REPLIES 7
di_niu0
Obsidian | Level 7

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

Kurt_Bremser
Super User

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
Obsidian | Level 7
Thank you! I need to transpose it afterwards. But I'm managed to get the right format.
Reeza
Super User
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
Obsidian | Level 7
Thank you! I think you are doing for every month. But I could modify it to every 6 months.
Reeza
Super User
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
Obsidian | Level 7
Yes. Thanks!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2134 views
  • 0 likes
  • 3 in conversation