topic Re: Generate visit dates in SAS Programming

Generate visit dates

SK_11 — Mon, 24 May 2021 00:12:04 GMT

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

Re: Generate visit dates

SK_11 — Mon, 24 May 2021 00:15:50 GMT

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;

Re: Generate visit dates

SK_11 — Mon, 24 May 2021 02:10:49 GMT

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

Re: Generate visit dates

Tom — Mon, 24 May 2021 02:30:32 GMT

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

Re: Generate visit dates

SK_11 — Mon, 24 May 2021 02:53:47 GMT

Thanks a lot Tom!

Re: Generate visit dates

SK_11 — Mon, 24 May 2021 03:07:15 GMT

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

Re: Generate visit dates

Tom — Mon, 24 May 2021 03:56:29 GMT

@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;

Re: Generate visit dates

SK_11 — Mon, 24 May 2021 03:57:15 GMT

Thanks