- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot Tom!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content