BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
VaibhavGhoghari
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
VaibhavGhoghari
Fluorite | Level 6

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

16 REPLIES 16
ballardw
Super User

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.

VaibhavGhoghari
Fluorite | Level 6

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.

 

Reeza
Super User

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

VaibhavGhoghari
Fluorite | Level 6

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

EEng
Obsidian | Level 7

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.
EEng
Obsidian | Level 7
Sorry the Saturday function are not working correctly.
VaibhavGhoghari
Fluorite | Level 6

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

By the way you guys are amazing!!!

EEng
Obsidian | Level 7
Corrected the Sat1 and Sat2
VaibhavGhoghari
Fluorite | Level 6

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.

 

 

 

 

VaibhavGhoghari
Fluorite | Level 6

Guys...Please suggest something...

EEng
Obsidian | Level 7

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.

VaibhavGhoghari
Fluorite | Level 6

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

EEng
Obsidian | Level 7

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
VaibhavGhoghari
Fluorite | Level 6

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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