Hi, please help me to write a code for the below request.
For first two model Build_Date will be today's date and for next four model Build_date is incremented by 1, it has to follow same sequence for next 6 models. The Build_Date
should exclude weekends and the list of Holidays we provide.
/* 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
Assuming your pattern will keep repeating, something like this will work.
/* Input data */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735
736
737
777
775
772
773
787
797
747
;
RUN;
/* Define your work days (code from: https://communities.sas.com/t5/SAS-Programming/macro-to-exclude-week-end-days-and-company-holidays-from-the/td-p/356141) */
data wrkdays (keep=begin);
format begin date9.;
array holidays(6);
do date = '16nov2021'd to '31dec2030'd ; **UPDATE with your date range;
if date eq intnx('year',date,0,'b') then do;
call missing(of holidays(*));
i=0;
end;
if date eq holiday('NEWYEAR', year(date)) or
date eq holiday('USINDEPENDENCE',year(date)) or
date eq holiday('THANKSGIVING', year(date)) or
date eq holiday('CHRISTMAS', year(date)) or
date eq holiday('MEMORIAL', year(date)) or
date eq holiday('LABOR', year(date)) then do; **UPDATE with Holidays you want to exclude;
i+1;
holidays(i)=date;
end;
if not(date in holidays or weekday(date) in (1,7)) then do;
season=1;
begin=date;
output;
end;
end;
run;
/* Generate final data */
options intervalds=(workdays=wrkdays); **define date interval for INTNX function;
data want (drop = counter);
set input;
counter + 1; **start a counter;
if counter = 7 then counter = 1; **reset the counter every 7th row, pattern restarts here;
retain Build_Date;
if _n_ = 1 then Build_Date = "16nov2021"d ; **first row should be 11/16/2021. Use today() if you want date of submit;
else if counter ne 2 then Build_Date = intnx('workdays', Build_Date, 1); **repeats previous date on counter = 2 and adds 1 work day otherwise, excludes weekends;
format Build_Date mmddyy10.;
run;
proc print data=want;
run;
The last row (model 747) here will have 11/26/2021 instead of 11/25/2021 which is Thanksgiving Day. You'll need to modify the wrkdays data set to include the Holidays you wish to exclude.
Apparently the models in rows 2 and 8 will have the same date value as the immediately preceding row. But every row except those at 2 modulo 6 should have date incremented by 1, skipping holidays and weekends.
If you are using US holidays, you can use the HOLIDAY functions as shown by @lopezr . You can put those dates in a hash object. Then just keep incrementing the date (except for row 2) until the new date is a weekday and is not in the hash object:
data want (drop=_:);
input model $10.;
retain date ;
format date weekdate22. ;
if _n_=1 then do;
*populate holidays hash object, keyed on DATE;
declare hash holidays();
holidays.definekey('date');
holidays.definedata('date');
holidays.definedone();
do _year=2021 to 2022;
do _holiday_name='USINDEPENDENCE','NEWYEAR','THANKSGIVING','CHRISTMAS','MEMORIAL','LABOR';
date=holiday(_holiday_name,_year);
holidays.add();
end;
end;
*initialize date to precede first model (assume first model will be TODAY() ) **;
date=today()-1;
end;
if mod(_n_,6)^=2 then do date=date+1 by 1 until (holidays.check()^=0 and weekday(date) in (2,3,4,5,6)) ; end;
datalines;
735
736
737
777
775
772
773
787
797
747
RUN;
Looks the like the problem you have posted some time ago: https://communities.sas.com/t5/SAS-Programming/Incrementing-the-dates/m-p/780750
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.