topic Re: Working with Dates and Times in SAS (Tutorial) in SAS Programming

Finding the next working day with a custom calendar

VaibhavGhoghari — Fri, 13 Jul 2018 14:50:47 GMT

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

Re: Working with Dates and Times in SAS (Tutorial)

ballardw — Fri, 13 Jul 2018 14:36:15 GMT

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.

Re: Working with Dates and Times in SAS (Tutorial)

VaibhavGhoghari — Fri, 13 Jul 2018 15:53:19 GMT

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.

Re: Working with Dates and Times in SAS (Tutorial)

Reeza — Fri, 13 Jul 2018 16:12:20 GMT

Unfortunately this isn't that straightforward. There are some interesting options, such as custom functions, but one way is illustrated here:

http://www.sascommunity.org/wiki/Generating_Holiday_Lists

Re: Working with Dates and Times in SAS (Tutorial)

VaibhavGhoghari — Fri, 13 Jul 2018 16:17:49 GMT

Thanks a lot for the document. I will look into it , try it once and will post my outcome.

Re: Finding the next working day with a custom calendar

EEng — Mon, 16 Jul 2018 13:31:46 GMT

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;

Obs date new_date

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

1	~~05/03/2018~~	.
2	~~05/06/2018~~	~~05/07/2018~~
3	~~07/08/2018~~	~~07/09/2018~~
4	~~10/19/2018~~	~~10/22/2018~~
5	~~08/30/2018~~	~~08/31/2018~~
6	~~08/31/2018~~	.

Re: Finding the next working day with a custom calendar

EEng — Fri, 13 Jul 2018 16:47:12 GMT

Sorry the Saturday function are not working correctly.

Re: Finding the next working day with a custom calendar

VaibhavGhoghari — Fri, 13 Jul 2018 16:53:37 GMT

Thanks for the program..Give me a day . Let me try...Hope it works!! ....

By the way you guys are amazing!!!

Re: Finding the next working day with a custom calendar

EEng — Fri, 13 Jul 2018 17:00:00 GMT

Corrected the Sat1 and Sat2

Re: Finding the next working day with a custom calendar

VaibhavGhoghari — Sat, 14 Jul 2018 08:27:03 GMT

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.

Re: Finding the next working day with a custom calendar

VaibhavGhoghari — Mon, 16 Jul 2018 05:37:22 GMT

Guys...Please suggest something...

Re: Finding the next working day with a custom calendar

EEng — Mon, 16 Jul 2018 13:28:52 GMT

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.

Re: Finding the next working day with a custom calendar

VaibhavGhoghari — Mon, 16 Jul 2018 16:27:28 GMT

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...

Re: Finding the next working day with a custom calendar

EEng — Mon, 16 Jul 2018 21:04:48 GMT

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:

Obs date new_date

1	03MAY2018	04MAY2018
2	06MAY2018	07MAY2018
3	26MAY2018	28MAY2018
4	30MAY2018	31MAY2018
5	08JUL2018	09JUL2018
6	19OCT2018	22OCT2018
7	30AUG2018	31AUG2018
8	31AUG2018	03SEP2018

Re: Finding the next working day with a custom calendar

VaibhavGhoghari — Tue, 17 Jul 2018 10:26:46 GMT

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....

Re: Finding the next working day with a custom calendar

Reeza — Tue, 17 Jul 2018 14:44:06 GMT

@VaibhavGhoghari please mark @EEng solution as correct, not your response.

Re: Finding the next working day with a custom calendar

VaibhavGhoghari — Sat, 21 Jul 2018 07:25:31 GMT

Thanks for the suggestion ...actually my project requirement got changed at the end so according i went ahead with some other logic...

It wasn't different completely but took the reference from your code ...