I am trying to generate dates where the first group will get a date say 7 days from today and the second date will be say 180 days from today, the next group will get a date next day and so on. The dates will be from Monday to Friday. I am facing issues around weekends. Any help in this will be greatly appreciated.
Sample data with output;
i | groups | Date1 | Date2 |
1 | 1 | 31-May-21 | 29-Nov-21 |
2 | 1 | 31-May-21 | 29-Nov-21 |
3 | 2 | 2-Jun-21 | 30-Nov-21 |
4 | 2 | 2-Jun-21 | 30-Nov-21 |
5 | 3 | 3-Jun-21 | 1-Dec-21 |
6 | 3 | 3-Jun-21 | 1-Dec-21 |
7 | 4 | 4-Jun-21 | 2-Dec-21 |
8 | 4 | 4-Jun-21 | 2-Dec-21 |
9 | 5 | 7-Jun-21 | 3-Dec-21 |
10 | 5 | 7-Jun-21 | 3-Dec-21 |
11 | 6 | 10-Jun-21 | 6-Dec-21 |
12 | 6 | 10-Jun-21 | 6-Dec-21 |
13 | 7 | 11-Jun-21 | 7-Dec-21 |
14 | 7 | 11-Jun-21 | 7-Dec-21 |
15 | 8 | 12-Jun-21 | 9-Dec-21 |
16 | 8 | 12-Jun-21 | 9-Dec-21 |
17 | 9 | 13-Jun-21 | 10-Dec-21 |
18 | 9 | 13-Jun-21 | 10-Dec-21 |
19 | 10 | 14-Jun-21 | 11-Dec-21 |
20 | 10 | 14-Jun-21 | 11-Dec-21 |
The current code:
data dates;
do i=1 to 20;
groups=ceil(i/2); output;
end;
run;
data &work..want;
Set &work..dates ;
format Date1 calldate2 d1 d2 date9.;
retain n d1 d2 ;
Date1=today()+7;
d1=Date1;
do n=1 to 10;
if groups=n then do;
Date1=d1+n-1;
if Weekday(Date1)=1 then Date1=Date1+1;
if Weekday(Date1)=7 then Date1=Date1+2;
CallDate2=Date1+180;
if Weekday(CallDate2)=1 then CallDate2=CallDate2+1;
if Weekday(CallDate2)=7 then CallDate2=CallDate2+2;
end;
d1=Date1+1;
d2=calldate2+1;
if Weekday(d1)=1 then d1=d1+1;
if Weekday(d1)=7 then d1=d1+2;
end;
drop n;
run;
Thanks
Use intnx() with WEEKDAY interval.
data group_dates;
date1=intnx('weekday',today()+7,0);
date2=intnx('weekday',today()+180,0);
do group=1 to 10;
output;
date1=intnx('weekday',date1,1);
date2=intnx('weekday',date2,1);
end;
format date1 date2 weekdate.;
run;
result
Obs date1 date2 group 1 Friday, May 28, 2021 Friday, November 19, 2021 1 2 Monday, May 31, 2021 Monday, November 22, 2021 2 3 Tuesday, June 1, 2021 Tuesday, November 23, 2021 3 4 Wednesday, June 2, 2021 Wednesday, November 24, 2021 4 5 Thursday, June 3, 2021 Thursday, November 25, 2021 5 6 Friday, June 4, 2021 Friday, November 26, 2021 6 7 Monday, June 7, 2021 Monday, November 29, 2021 7 8 Tuesday, June 8, 2021 Tuesday, November 30, 2021 8 9 Wednesday, June 9, 2021 Wednesday, December 1, 2021 9 10 Thursday, June 10, 2021 Thursday, December 2, 2021 10
Updated code:
data dates;
do i=1 to 20;
groups=ceil(i/2); output;
end;
run;
data &work..want;
Set &work..dates ;
format Date1 Date2 d1 d2 date9.;
retain n d1 d2 ;
Date1=today()+7;
d1=Date1;
do n=1 to 10;
if groups=n then do;
Date1=d1+n-1;
if Weekday(Date1)=1 then Date1=Date1+1;
if Weekday(Date1)=7 then Date1=Date1+2;
Date2=Date1+180;
if Weekday(Date2)=1 then Date2=Date2+1;
if Weekday(Date2)=7 then Date2=Date2+2;
end;
d1=Date1+1;
d2=Date2+1;
if Weekday(d1)=1 then d1=d1+1;
if Weekday(d1)=7 then d1=d1+2;
end;
drop n;
run;
Sorry for the wrong out, the correct output will be (all weekdays)
i | groups | Date1 | Date2 |
1 | 1 | 31-May-21 | 29-Nov-21 |
2 | 1 | 31-May-21 | 29-Nov-21 |
3 | 2 | 2-Jun-21 | 30-Nov-21 |
4 | 2 | 2-Jun-21 | 30-Nov-21 |
5 | 3 | 3-Jun-21 | 1-Dec-21 |
6 | 3 | 3-Jun-21 | 1-Dec-21 |
7 | 4 | 4-Jun-21 | 2-Dec-21 |
8 | 4 | 4-Jun-21 | 2-Dec-21 |
9 | 5 | 7-Jun-21 | 3-Dec-21 |
10 | 5 | 7-Jun-21 | 3-Dec-21 |
11 | 6 | 10-Jun-21 | 6-Dec-21 |
12 | 6 | 10-Jun-21 | 6-Dec-21 |
13 | 7 | 11-Jun-21 | 7-Dec-21 |
14 | 7 | 11-Jun-21 | 7-Dec-21 |
15 | 8 | 14-Jun-21 | 8-Dec-21 |
16 | 8 | 14-Jun-21 | 8-Dec-21 |
17 | 9 | 15-Jun-21 | 9-Dec-21 |
18 | 9 | 15-Jun-21 | 9-Dec-21 |
19 | 10 | 16-Jun-21 | 10-Dec-21 |
20 | 10 | 16-Jun-21 | 10-Dec-21 |
Use intnx() with WEEKDAY interval.
data group_dates;
date1=intnx('weekday',today()+7,0);
date2=intnx('weekday',today()+180,0);
do group=1 to 10;
output;
date1=intnx('weekday',date1,1);
date2=intnx('weekday',date2,1);
end;
format date1 date2 weekdate.;
run;
result
Obs date1 date2 group 1 Friday, May 28, 2021 Friday, November 19, 2021 1 2 Monday, May 31, 2021 Monday, November 22, 2021 2 3 Tuesday, June 1, 2021 Tuesday, November 23, 2021 3 4 Wednesday, June 2, 2021 Wednesday, November 24, 2021 4 5 Thursday, June 3, 2021 Thursday, November 25, 2021 5 6 Friday, June 4, 2021 Friday, November 26, 2021 6 7 Monday, June 7, 2021 Monday, November 29, 2021 7 8 Tuesday, June 8, 2021 Tuesday, November 30, 2021 8 9 Wednesday, June 9, 2021 Wednesday, December 1, 2021 9 10 Thursday, June 10, 2021 Thursday, December 2, 2021 10
Thanks a lot Tom!
Hi Tom
This code generates sequential weekdays, how do I make changes if I have many observation under each group and each group will get the same date?
Thanks
@SK_11 wrote:
Hi Tom
This code generates sequential weekdays, how do I make changes if I have many observation under each group and each group will get the same date?
Thanks
I don't understand the question. If you have a dataset with a GROUP variable on it then just merge this little dataset onto the exiting dataset BY the GROUP variable and the two new DATE variables will get copied onto all members of that group.
If you don't want to build the group_dates dataset first then just you could try using the existing dataset and by group processing.
Here is template. Fill in the logic for calculating the initials dates and the logic for incrementing it into the appropriate place.
data want;
set have;
by group;
if _n_=1 then do;
date1 = ...;
date2 = ...;
end;
else if first.group then do;
date1 = ...;
date2 = ...;
end;
retain date1 date2 ;
format date1 date2 yymmdd10.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.