/* 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
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;
Please help me with the dynamic SAS code. And the Build_Date should be weekday.
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;
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.
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;
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
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.
Sorry. Thank you ! 🙂
Hi, I am unable to solve it . Please help me with offset values for each of my query .
Show us what you have tried, so we can point out where you erred.
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;
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
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
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.
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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: