DATA Step, Macro, Functions and more

Finding the next working day with a custom calendar

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Finding the next working day with a custom calendar

[ Edited ]

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


Accepted Solutions
Solution
Tuesday
Occasional Contributor
Posts: 10

Re: Finding the next working day with a custom calendar

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

 

 

View solution in original post


All Replies
Super User
Posts: 13,583

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

Posted in reply to VaibhavGhoghari

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.

Occasional Contributor
Posts: 10

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

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.

 

Super User
Posts: 23,774

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

Posted in reply to VaibhavGhoghari

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

Occasional Contributor
Posts: 10

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

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

Occasional Contributor
Posts: 17

Re: Finding the next working day with a custom calendar

[ Edited ]
Posted in reply to VaibhavGhoghari

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
Obs date new_date
105/03/201805/04/2018
205/06/201805/07/2018
305/30/201805/31/2018
407/08/201807/09/2018
510/19/201810/22/2018
608/30/201808/31/2018
708/31/2018

09/03/2018

 

Ignore below...not sure how to remove

105/03/2018.
205/06/201805/07/2018
307/08/201807/09/2018
410/19/201810/22/2018
508/30/201808/31/2018
608/31/2018.
Occasional Contributor
Posts: 17

Re: Finding the next working day with a custom calendar

Sorry the Saturday function are not working correctly.
Occasional Contributor
Posts: 10

Re: Finding the next working day with a custom calendar

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

By the way you guys are amazing!!!

Occasional Contributor
Posts: 17

Re: Finding the next working day with a custom calendar

Posted in reply to VaibhavGhoghari
Corrected the Sat1 and Sat2
Occasional Contributor
Posts: 10

Re: Finding the next working day with a custom calendar

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.

 

 

 

 

Occasional Contributor
Posts: 10

Re: Finding the next working day with a custom calendar

Posted in reply to VaibhavGhoghari

Guys...Please suggest something...

Occasional Contributor
Posts: 17

Re: Finding the next working day with a custom calendar

[ Edited ]
Posted in reply to VaibhavGhoghari

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.

Occasional Contributor
Posts: 10

Re: Finding the next working day with a custom calendar

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

Occasional Contributor
Posts: 17

Re: Finding the next working day with a custom calendar

Posted in reply to VaibhavGhoghari

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
103MAY201804MAY2018
206MAY201807MAY2018
326MAY201828MAY2018
430MAY201831MAY2018
508JUL201809JUL2018
619OCT201822OCT2018
730AUG201831AUG2018
831AUG201803SEP2018
Solution
Tuesday
Occasional Contributor
Posts: 10

Re: Finding the next working day with a custom calendar

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 182 views
  • 0 likes
  • 4 in conversation