topic Re: Incrementing the dates in SAS Programming

Incrementing the dates

vidyasagar1 — Tue, 09 Nov 2021 12:42:43 GMT

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

looking for the output:

/* For the first two models Build_Date will be today's date and for next three models Build_Date will be tomorrow's and the trend will continue for next five. */;

Model Build_Date

735 11/09/2021

736 11/09/2021

737 11/10/2021

777 11/10/2021

775 11/10/2021

772 11/11/2021

773 11/11/2021

787 11/12/2021

797 11/12/2021

747 11/12/2021

Re: Incrementing the dates

vidyasagar1 — Tue, 09 Nov 2021 12:50:16 GMT

Please help me with the dynamic SAS code. And the Build_Date should be weekday.

Re: Incrementing the dates

Kurt_Bremser — Tue, 09 Nov 2021 13:08:24 GMT

Every day is a day of a week, so please define "weekday".

This code creates your dataset:

data have;
input model $10.;
datalines;
735    
736     
737       
777
775
772
773
787
797
747
;

%let start=%eval(%sysfunc(today())-1);

data want;
set have;
retain
  flag_3 0
  count 0
  build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
  build_date + 1;
  count = 1 + flag_3;
  flag_3 = not flag_3;
end;
else count + (-1);
drop count flag_3;
run;

Re: Incrementing the dates

vidyasagar1 — Tue, 09 Nov 2021 13:34:15 GMT

Hi, I am very happy for your quick reply. This code is working as expected for my request. Sorry I forgot to add few more points. The Build_Date should have only working days, means excludling Saturday's and Sunday's date and also the holiday's.

We will feed the list of Holidays.

Please help me with the SAS code including the above points also.

Re: Incrementing the dates

Kurt_Bremser — Tue, 09 Nov 2021 13:44:56 GMT

So, you have a dataset containing dates of non-workdays. Make a list out of that, which you then use in a IN condition:

proc sql;
select holiday format=5. into :holidays separated by ","
from holidays;
quit;

data want;
set have;
retain
  flag_3 0
  count 0
  build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
  do until (build_date not in(&holidays.) and weekday(build_date) not in (1,7));
    build_date + 1;
  end;
  count = 1 + flag_3;
  flag_3 = not flag_3;
end;
else count + (-1);
drop count flag_3;
run;

Re: Incrementing the dates

vidyasagar1 — Tue, 09 Nov 2021 17:22:33 GMT

Hi ,

Could you also please help me to write a code for the below requests also ?

Request 1 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first two models today's date and next two model's tomorrow's date and so on.

Model Build_Date

735 11/09/2021

736 11/09/2021

737 11/10/2021

777 11/10/2021

775 11/11/2021

772 11/11/2021

773 11/12/2021

787 11/12/2021

797 11/13/2021

747 11/13/2021

Request 2 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first three models today's date and next three model's tomorrow's date and so on.

Model Build_Date

735 11/09/2021

736 11/09/2021

737 11/09/2021

777 11/10/2021

775 11/10/2021

772 11/10/2021

773 11/11/2021

787 11/11/2021

797 11/11/2021

747 11/12/2021

Request 3 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first model today's date and next model day after tomorrow's date and so on.

Model Build_Date

735 11/09/2021

736 11/11/2021

737 11/13/2021

777 11/15/2021

775 11/17/2021

772 11/19/2021

773 11/21/2021

787 11/23/2021

797 11/25/2021

747 11/27/2021

Request 4 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first model today's date and next model tomorrow's date and so on.

Model Build_Date

735 11/09/2021

736 11/10/2021

737 11/11/2021

777 11/12/2021

775 11/13/2021

772 11/14/2021

773 11/15/2021

787 11/16/2021

797 11/17/2021

747 11/18/2021

Re: Incrementing the dates

Kurt_Bremser — Tue, 09 Nov 2021 17:31:22 GMT

It's the same logic, with different offset value. You can easily solve that on your own now.

Like other muscles, an unused brain will atrophy.

Re: Incrementing the dates

PaigeMiller — Tue, 09 Nov 2021 18:12:37 GMT

%let today=%sysfunc(today());
%let replication=2;
data want;
    set input;
    date=intnx('weekday17W',&today,floor((_n_-1)/&replication));
    format date mmddyy10.;
run;

You can change the value of &REPLICATION, right now it is set to 2, so every two observations will have the same date, and the next observation will be the next weekday. By changing &REPLICATION, you get most of what you have asked for.

If you set &REPLICATION to 2.5, the first 3 records will have the same date, and then the next 2 records will have the next weekday, and so on. If you want the first 2 records to have the same weekday, and then the next 3 records to have the same weekday, set &REPLICATION to 2.5 and then make the calculation of date via the following:

    date=intnx('weekday17W',&today,floor((_n_-1e-6)/&replication));

Re: Incrementing the dates

vidyasagar1 — Wed, 10 Nov 2021 08:48:53 GMT

Sorry. Thank you ! 🙂

Re: Incrementing the dates

vidyasagar1 — Wed, 10 Nov 2021 08:51:18 GMT

Thanks a lot. This solution is working for me . 🙂

Re: Incrementing the dates

vidyasagar1 — Tue, 16 Nov 2021 08:21:49 GMT

Hi, sorry how to exclude the list of holidays also from that expression ?

Re: Incrementing the dates

vidyasagar1 — Tue, 16 Nov 2021 13:44:49 GMT

Hi, I am unable to solve it . Please help me with offset values for each of my query .

Re: Incrementing the dates

Kurt_Bremser — Tue, 16 Nov 2021 14:13:45 GMT

Show us what you have tried, so we can point out where you erred.

Re: Incrementing the dates

vidyasagar1 — Tue, 16 Nov 2021 16:08:10 GMT

Finally I developed the code for the my requests and it is working fine.

%let start=%eval(%sysfunc(today())-1);

Code for Request 1 :

data want;
set have;
retain
flag_3 1
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 2 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

Code for Request 2 :

data want;
set have;
retain
flag_3 1
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 3 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

Code for Request 3 :

data want;
set have;
retain
flag_3 2
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 3 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

Code for Request 4 :

data want;
set have;
retain
flag_3 1
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 1 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

Re: Incrementing the dates

vidyasagar1 — Tue, 16 Nov 2021 16:35:48 GMT

For this below request I am unable to develop the code.

For first two models today's date and for next four models Build_date is incremented by 1, it has to follow same sequence for next 6 models.

New Request :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735
736
737
777
775
772
773
787
797
747
;
RUN;
Expected Output:

Model Build_Date

735 11/16/2021

736 11/16/2021

737 11/17/2021

777 11/18/2021

775 11/19/2021

772 11/22/2021

773 11/23/2021

787 11/23/2021

797 11/24/2021

747 11/25/2021

Re: Incrementing the dates

Tom — Tue, 16 Nov 2021 16:30:02 GMT

Just use two nested DO loops.

DATA INPUT;
  INPUT MODEL $10.;
DATALINES;
735
736
737
777
775
772
773
787
797
747
;

data want;
  do build_date=today() by 1 ;
    do i=1 to 3;
      set input;
      output;
    end;
  end;
  format build_date yymmdd10.;
run;

proc print;
run;

Obs    build_date    i    MODEL

  1    2021-11-16    1     735
  2    2021-11-16    2     736
  3    2021-11-16    3     737
  4    2021-11-17    1     777
  5    2021-11-17    2     775
  6    2021-11-17    3     772
  7    2021-11-18    1     773
  8    2021-11-18    2     787
  9    2021-11-18    3     797
 10    2021-11-19    1     747

Re: Incrementing the dates

vidyasagar1 — Tue, 16 Nov 2021 16:38:11 GMT

Sorry, I have corrected my query. Please help me to write the code for my corrected query. It has to exclude weekends and list of holiday's which we provide.

Re: Incrementing the dates

Tom — Tue, 16 Nov 2021 17:00:03 GMT

Just incorporate the logic to skip the holidays.

For example if have a list of holidays in a dataset named HOLIDAYS (and the list is short enough to put into a macro variable) then something like this should work.

Let's test it by pretending tomorrow is a holiday.

data holidays;
  holiday=today()+1;
run;

proc sql;
  select holiday into :holidays separated by ' '
  from holidays;
quit;

data want;
  do build_date=today() by 1 ;
    do build_date=build_date by 1
       while(build_date in (&holidays) or weekday(build_date) in (1 7));
    end;
    do i=1 to 3;
      set input;
      output;
    end;
  end;
  format build_date yymmdd10.;
run;

Result

Obs    build_date    i    MODEL

  1    2021-11-16    1     735
  2    2021-11-16    2     736
  3    2021-11-16    3     737
  4    2021-11-18    1     777
  5    2021-11-18    2     775
  6    2021-11-18    3     772
  7    2021-11-19    1     773
  8    2021-11-19    2     787
  9    2021-11-19    3     797
 10    2021-11-22    1     747

So you can see it skipped tomorrow and also the week-end.

Re: Incrementing the dates

vidyasagar1 — Wed, 17 Nov 2021 08:58:37 GMT

Hi, Thank you for trying to help me. But my expected output is like below :

For the first two models today's date and for next four models incrementing the date by 1 and so on. Also should not consider weekends and list of holidays we provide.

Expected Output:

Model Build_Date

735 11/16/2021

736 11/16/2021

737 11/17/2021

777 11/18/2021

775 11/19/2021

772 11/22/2021

773 11/23/2021

787 11/23/2021

797 11/24/2021

747 11/25/2021

Re: Incrementing the dates

vidyasagar1 — Wed, 17 Nov 2021 14:43:37 GMT

Hi All, please help me to write a code for this.