topic Re: Need to check the holiday in SAS Programming

Need to check the holiday

dprasa8 — Tue, 17 Sep 2013 00:33:30 GMT

cHi Friends,

I need to create a macro variable which should capture a working date and this is to automate my code.

condition 1: if yesterday is working day then macro variable value is yesterday's date.

(holidays can be weekends and list of festivals).

condition 2: if yesterday is holiday then I need to get the previous working day.

(Suppose holidays might be continues for a week or just 1 day, I need to go back day-by-day and find the previous working day).

Please help me with sample code ASAP.

Thanks for you help!!

Durga.

Re: Need to check the holiday

SASKiwi — Tue, 17 Sep 2013 01:10:53 GMT

Try this. It doesn't cater for public holidays but it works for weekdays. Since public holidays vary so much you could deal with these with a hard-coded override.

%let Last_Busday = %sysfunc(intnx(WEEKDAY, "&sysdate"d, -1), date9.);

%put Last_Busday = &Last_Busday;

Re: Need to check the holiday

dprasa8 — Tue, 17 Sep 2013 04:14:57 GMT

Thank You!!

I need to check the public holidays as well, suppose holidays are continuously from dec20 to dec 25.

during these days macro variable should catch dec19.

Re: Need to check the holiday

FrankPoppe — Tue, 17 Sep 2013 14:53:32 GMT

How to cater for holidays as well would depend on how you store the information on holidays. Is it a SAS data set, and is it then a list of days or a list of periods (with from and to dates)? Or is a macrovariable containing a list of dates? Or etc...

Re: Need to check the holiday

ballardw — Tue, 17 Sep 2013 16:38:31 GMT

Also, you might want to indicate which country's holidays you are concerned with ...

Here's a macro I pulled off SAS.COM awhile back that generates the dates of US holidays for specified years that may have some helpful ideas

%macro dummy;

/* Determine dates for various calendar events.*/
DATA _NULL_;
file print;
%DO YEAR=2011 %to 2020;

    /* FDO-prefixed variables are First Day Of... For */
    /* example, FDOY is First Day of Year. */
FDOY=MDY(1,1,&YEAR);
NYD = FDOY;
NYD_HOL = NYD;
IF WEEKDAY(NYD) = 1 THEN NYD_HOL=NYD+1;
IF WEEKDAY(NYD) = 7 THEN NYD_HOL=NYD-1;
    /* Martin Luther King Day. */
MLK_DAY=INTNX('WEEK.2',FDOY,(WEEKDAY(FDOY) NE 2)+2);

/* Presidents' Day. */
FDO_FEB=INTNX('MONTH',FDOY,1);
PRES_DAY=INTNX('WEEK.2',FDO_FEB,(WEEKDAY(FDO_FEB) NE 2)+2);

/* Memorial Day. */
FDO_MAY=INTNX('MONTH',FDOY,4);
MEM_DAY=INTNX('WEEK.2',FDO_MAY,(WEEKDAY(FDO_MAY) IN (1,7))+4);

/* Independence Day. */
IND_DAY=MDY(7,4,&YEAR);
IND_HOL=IND_DAY;
IF WEEKDAY(IND_DAY) = 1 THEN IND_HOL=IND_DAY+1;
IF WEEKDAY(IND_DAY) = 7 THEN IND_HOL=IND_DAY-1;

/* Labor Day. */
FDO_SEP=INTNX('MONTH',FDOY,8);
LAB_DAY=INTNX('WEEK.2',FDO_SEP,(WEEKDAY(FDO_SEP) NE 2));

/* Columbus Day. */
FDO_OCT=INTNX('MONTH',FDOY,9);
COL_DAY=INTNX('WEEK.2',FDO_OCT,(WEEKDAY(FDO_OCT) NE 2)+1);

/* Election Day. */
FDO_NOV=INTNX('MONTH',FDOY,10);
ELEC_DAY=INTNX('WEEK.3',FDO_NOV,1);

/* Veterans Day. */
VET_DAY=MDY(11,11,&YEAR);
VET_HOL = VET_DAY;
IF WEEKDAY(VET_DAY) = 1 THEN VET_HOL=VET_DAY+1;
IF WEEKDAY(VET_DAY) = 7 THEN VET_HOL=VET_DAY-1;

/*Thanksgiving Day */
THKS_DAY=INTNX('WEEK.5',FDO_NOV,(WEEKDAY(FDO_NOV) NE 5)+3);

    /* Christmas Day. */
XMAS_DAY=MDY(12,25,&YEAR);
XMAS_HOL=XMAS_DAY;
IF WEEKDAY(XMAS_DAY) = 1 THEN XMAS_HOL=XMAS_DAY+1;
IF WEEKDAY(XMAS_DAY) = 7 THEN XMAS_HOL=XMAS_DAY-1;
xwkday=weekday(xmas_day);

    /* Write results to SAS Log. */
PUT
    "New Year's Day Observed," NYD_HOL yymmdds10. /
    "MLK Day," MLK_DAY    yymmdds10. /
    "President's Day," PRES_DAY   yymmdds10. /
    "Memorial Day," MEM_DAY    yymmdds10. /
    "Independence Day Observed," IND_HOL    yymmdds10. /
    "Labor Day," LAB_DAY    yymmdds10. /
    "Columbus Day," COL_DAY    yymmdds10. /
    "Veteran's Day Observed," VET_HOL    yymmdds10. /
    "Thanksgiving," THKS_DAY    yymmdds10. /
    "Christmas Observed," XMAS_HOL    yymmdds10. / @@
;
%end;
RUN;

%mend;

%dummy;

Re: Need to check the holiday

Reeza — Tue, 17 Sep 2013 17:04:53 GMT

If you have custom dates you need to factor in, I usually create a date table/dimension, but this is because I use it for other things as well.

Anyways, here' s my code. You need to change the section where I hardcode in the holidays/nonwork days to match wherever you live. You can replace &test_date with date() for production.

%MACRO DATE_DIMENSION(startdate=, enddate=, outfil=);

data &outfil;

retain number_workdays;

do _n_ = &startdate to &enddate;

date_id + 1;

date = _n_;

format date mmddyy10.;

month = month(date);

calendar_month = year(date)*100+month(date);

day = day(date);

year = year(date);

quarter = mod(qtr(date)+2,4)+1;

length fiscal_year $10.;

if quarter in (2, 3,4) then fiscal_year=compress(cat(year ,"/",year+1));

else if quarter=1 then fiscal_year=compress(cat(year-1,"/",year));

format fiscal_year $10.;

length fiscal_year $10.;

dayofweek=weekday(date);

if dayofweek >= 2 and dayofweek<=6 then workday=1; else workday=0;

*Change this section to match your business needs;

if date in ('01JAN2013'd,'18FEB2013'd,'29Mar2013'd '01APR2013'd,'22MAY2013'd,

'01JUL2013'd,'05AUG2013'd,'02SEP2013'd,'14OCT2013'd,'11NOV2013'd,

'25DEC2013'd,'26DEC2013'd, '27Dec2013'd) then isholiday=1; else isholiday=0;

if isholiday then workday=0;

if day=1 and month=1 then number_workdays=0;

number_workdays+workday;

output;

end;

run;

%MEND DATE_DIMENSION;

%date_dimension(startdate='01jan2013'd, enddate='31Dec2013'd,outfil=date_dimension)

*get previous work day;

%let test_date="01Feb2013"d;

proc sql;

select max(date) format=date9. into :prev_work_day from

date_dimension where workday=1 and date < &test_date;

quit;

%put &prev_work_day;

Re: Need to check the holiday

dprasa8 — Tue, 17 Sep 2013 17:27:45 GMT

Hello- I have prepared data set which has dates and holidays list.

needed logic to check for prev-working day.

code:

options symbolgen mlogic mprint;

%macro abc;

ddmmyy9.;

dt= 1 %to 365;

'day','31-dec-2012'd,REP,'e');

downame10.);

'day','31-dec-2012'd,REP,'e'),DDMMYY9.);

;

%mend abc;

print data = dates;

;

calender;

dates;

(rep_date in('02-jan-2013'd,'14-jan-2013'd,'15-jan-2013'd,'16-jan-2013'd,'01-mar-2013'd))

1,7)) then holiday = 'Y'; else holiday = 'N';

;

print;

holiday ='Y';run;

Re: Need to check the holiday

dprasa8 — Tue, 17 Sep 2013 17:29:32 GMT

Thanks all !!.

I have tried Reeza's approach and it working fine.

Thanks for such great response

Re: Need to check the holiday

jakarman — Tue, 17 Sep 2013 17:54:50 GMT

Customized date date datetime datasets are part of the SAS concepts.

SAS(R) 9.3 Functions and CALL Routines: Reference (Date and Time Intervals)

No need to invent/code it yourself anymore.

Re: Need to check the holiday

Reeza — Tue, 17 Sep 2013 18:11:09 GMT

True, but if you need to define you own intervals the amount of code is about the same.

And either way you have to remember to update it, because SAS mainly recognizes US holidays, Canadian holidays are different, so again, a different set of customization required.

Re: Need to check the holiday

jakarman — Tue, 17 Sep 2013 18:26:39 GMT

True, in a standalone situation you are on your own, it doesn't matter.

Having a more professional server based installation with a lot of users it makes more sense to have it defined central as a standardized normalization approach.

Reuse of tested and supported solutions should improve the "quality" and "time to market" wuth decreased programming time (cost).

Re: Need to check the holiday

Amir — Wed, 18 Sep 2013 13:07:59 GMT

Hi,

A bit late to the party, but your initial description reminded me of recursion:

/* set up list of holidays in a macro variable */

%let holidays=%sysfunc(catx(%str( ),"20dec13"d,"21dec13"d,"22dec13"d,"23dec13"d,"24dec13"d,"25dec13"d));

%put holidays=&holidays;

/* define macro using recursion */

%macro last_working_day(check_day);

%if %index(&holidays,&check_day) or %index(17,%sysfunc(weekday(&check_day))) %then

%last_working_day(%eval(&check_day-1));

%else

&check_day;

%mend last_working_day;

/* test dates */

%put last working day before 24dec13 is %last_working_day(%sysfunc(inputn(24dec13,anydtdte7.)));

%put last working day before 25dec13 is %last_working_day(%sysfunc(inputn(25dec13,anydtdte7.)));

%put last working day before 26dec13 is %last_working_day(%sysfunc(inputn(26dec13,anydtdte7.)));

Regards,

Amir.

Re: Need to check the holiday

dprasa8 — Thu, 19 Sep 2013 04:48:47 GMT

Awesome. Thanks Amir.

Re: Need to check the holiday

FrankPoppe — Thu, 19 Sep 2013 10:08:50 GMT

I like the recursive solution of Amir very much. Very elegant!

But I have two comments.

I think the last working dat BEFORE the 26th should be the 19th, not the 26th. To accomplish that I moved the subtraction to the start of the macro.
The treatment of the switch between human readable date and SAS internal data values and back could be more user friendly.

I changed Amir's code to the following, where I use the CATX trick a second time.

If you need the SAS data value as the result of macro you should of course remove the sysfunc(putn(...)) call at the end of the macro.

(And I left out the 21st and 22nd of the list of dates, as they will be skipped anyway as weekend days.)

/* set up list of holidays in a macro variable */
%let holidates=20dec13,23dec13,24dec13,25dec13;
/* and change the list of dates to a list of sas date values */
%let holidays=%sysfunc(catx(%str( ),"%sysfunc(catx("d%str(,)",&holidates))"d));
%put holidays=&holidays;

/* define macro using recursion */
%macro last_working_day(check_day);
     %let check_num=%sysfunc(inputn(&check_day,anydtdte7.));
     %let check_num =%eval(&check_num-1);
     
  %if %index(&holidays,&check_num) or %index(17,%sysfunc(weekday(&check_num))) %then
    %last_working_day(%sysfunc(putn(&check_num,date.)));
  %else
    %sysfunc(putn(&check_num,nldate.));
%mend last_working_day;

%put holidays=&holidays;

/* test dates */
%put last working day before 24dec13 is %last_working_day(24dec13);
%put last working day before 25dec13 is %last_working_day(25dec13);
%put last working day before 26dec13 is %last_working_day(26dec13);
%put last working day before 27dec13 is %last_working_day(27dec13);

Frank

Re: Need to check the holiday

Amir — Thu, 19 Sep 2013 11:26:07 GMT

Hi,

A definite improvement. I should have read the spec again and I think we all knew the date handling wasn't pretty!

Regards,

Amir.