Hi,
I have a data with a single date column and want to create another date column.
Condition:
If the value in the provided date column is either Sunday or Holiday as per my company's list or 1st and 2nd Saturday
which is also a holiday in our company then i want the next date which is a working day in my new date column.
Any help would be appreciated.
Thanks
Thank you so very much for your information and by considering your code as my reference i am done with my most of the logic....
What have you tried so far?
Have you investigated functions such as INTNX, HOLIDAY, HOLIDAYNAME, HOLIDAYTEST or WEEKDAY?
The HOLIDAY, HOLIDAYNAME, HOLIDAYTEST functions may or may not help depending on your locale as the function by default works with common United States and Canadian Holidays though Proc Datekeys and the EVENTDS option is one way to add "custom" holidays not supported by the Holiday functions by default.
Hi,
I have tried using intnx function with weekday1w option like below.
next_date = intnx('weekday1w',fr_date,1,'S');
and the result would be for all other days except sunday.
but how should i proceed if the value in the date column contains either 1st and 2nd saturday or a holiday.
If you can share the logic along with the syntax that would be of great help.
Thanks.
Unfortunately this isn't that straightforward. There are some interesting options, such as custom functions, but one way is illustrated here:
Thanks a lot for the document. I will look into it , try it once and will post my outcome.
You can try something like this, I wasn't sure what you wanted if the date did not meet your criteria.
%let tday = %sysfunc(today());
%macro custom;
%global custom1 custom2 custom3 custom4 custom5 custom6 custom7 sat1 sat2;
data _null_;
custom1='03feb2018'd;
custom2='10apr2018'd;
custom3='15may2018'd;
custom4='23jul2018'd;
custom5='30aug2018'd;
custom6='19oct2018'd;
custom7='21dec2018'd;
sat1=intnx('week',intnx('month',&tday,-0,'begin'),+0,'end');
sat2=intnx('week',intnx('month',&tday,-0,'begin'),+1,'end');
call symput('custom1',put(custom1,mmddyy10.));
call symput('custom2',put(custom2,mmddyy10.));
call symput('custom3',put(custom3,mmddyy10.));
call symput('custom4',put(custom4,mmddyy10.));
call symput('custom5',put(custom5,mmddyy10.));
call symput('custom6',put(custom6,mmddyy10.));
call symput('custom7',put(custom7,mmddyy10.));
call symput('sat1',put(sat1,mmddyy10.));
call symput('sat2',put(sat2,mmddyy10.));
%mend custom;
%custom;
run;
data xxxx;
input date date9.;
datalines;
03may2018
06may2018
08jul2018
19oct2018
30aug2018
31aug2018
;
data zzzz (drop=q cust);
set xxxx;
q=0;
if weekday(date)=1 then q+1;
do cust="&custom1","&custom2","&custom3","&custom4","&custom5","&custom6","&custom7","&sat1","&sat2";
if input(cust,mmddyy10.)=date then q+1;
end;
if q>0 then new_date=intnx('weekday',date,+1);
else new_date=intnx('weekday',date,+1);
run;
proc print data=zzzz;
format date new_date mmddyy10.;
run;
1 | 05/03/2018 | 05/04/2018 |
2 | 05/06/2018 | 05/07/2018 |
3 | 05/30/2018 | 05/31/2018 |
4 | 07/08/2018 | 07/09/2018 |
5 | 10/19/2018 | 10/22/2018 |
6 | 08/30/2018 | 08/31/2018 |
7 | 08/31/2018 | 09/03/2018 |
Ignore below...not sure how to remove
Thanks for the program..Give me a day . Let me try...Hope it works!! ....
By the way you guys are amazing!!!
Thanks for the code..... It is amazing..
Want some more help from you..
new_date value for 30th may and 31st Aug is coming blank so instead of that can i have a next day value like
for 30th may 2018 the new_date = 31st may 2018 as its a working day
for 31st aug 2018 the new_date = 3rd september 2018 which is a working day after 1st sept and 2nd sept which
are 1st saturday and Sunday respectively.
Also can you please explain the logic which you have made using if then and do statements.
Once again thanks in advance.
Guys...Please suggest something...
Hello, like I said earlier I wasn't sure what you wanted to do if it did not meet your criteria. You add this line after:
if q>0 then new_date=intnx('weekday',date,+1);
else new_date=intnx('weekday',date,+1);
Code has been updated. This should get you what you needed.
Here is my code :
data xxxx;
input date date9.;
format date date9.;
datalines;
08JUL2018
09JUL2018
30AUG2018
21DEC2018
;
data zzzz (drop=cust);
set xxxx;
J = 0;
new_time = date;
do While(J = 0);
if put(new_time,date9.) in
('30AUG2018'
,'21DEC2018',
,'15AUG2018'
,'16AUG2018'
)
then new_time = intnx('day',new_time,+1);
else if weekday(new_time)=1 then new_time=intnx('day',new_time,+1);
else J = 1;
end;
put new_time=;
put date=;
format new_time date9.;
run;
The dates mentioned below are my holiday list which will go on increasing or will remain constant every year.
if put(new_time,date9.) in
('30AUG2018'
,'21DEC2018',
,'15AUG2018'
,'16AUG2018'
)
then new_time = intnx('day',new_time,+1);
So it is not possible for me to write more and more values . So can we by somehow macrotise the code in if then else statement.
Thanks...
Try this out.
Your Holiday list can be an external file (SAS, csv, txt), it will always grow or constant when you enter a new year.
data holiday;
input date date9.;
datalines;
30AUG2018
21DEC2018
15AUG2018
16AUG2018
;
run;
data datain;
input date date9.;
datalines;
03may2018
06may2018
26may2018
30may2018
08jul2018
19oct2018
30aug2018
31aug2018
;
run;
%macro max(data=holiday);
%global cnt;
proc sql noprint;
select count(*) into :OBScnt from &data;
quit;
%let cnt=&OBScnt;
%mend max;
%max;
run;
proc transpose data=holiday out=outdata (drop=_name_) prefix=dte;
run;
data test (drop=q i cust dte1-dte&cnt);
set datain;
if _n_=1 then set outdata;
q=0;
array hol{*} dte1-dte&cnt;
if weekday(date)=1 then q+1;
do i=1 to &cnt;
if date=hol(i) then q+1;
end;
if q>0 then new_date=intnx('weekday',date,+1);
else new_date=intnx('weekday',date,+1);
run;
proc print data=test;
format date new_date date9.;
run;
Data Holiday will contain all your holidays.
Datain is your input file.
You can Transpose your Holiday file and apply it to every record (may not be the most efficient way)
Then apply your new_date logic. I thought you wanted the next Business day and not the day after? You can change this where you see fit.
Based on the above code, you should see this:
1 | 03MAY2018 | 04MAY2018 |
2 | 06MAY2018 | 07MAY2018 |
3 | 26MAY2018 | 28MAY2018 |
4 | 30MAY2018 | 31MAY2018 |
5 | 08JUL2018 | 09JUL2018 |
6 | 19OCT2018 | 22OCT2018 |
7 | 30AUG2018 | 31AUG2018 |
8 | 31AUG2018 | 03SEP2018 |
Thank you so very much for your information and by considering your code as my reference i am done with my most of the logic....
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.