09-16-2013 08:33 PM
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!!
09-16-2013 09:10 PM
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;
09-17-2013 10:53 AM
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...
09-17-2013 01:27 PM
Hello- I have prepared data set which has dates and holidays list.
needed logic to check for prev-working day.
options symbolgen mlogic mprint;
dt= 1 %to 365;
print data = dates;
1,7)) then holiday = 'Y'; else holiday = 'N';
09-17-2013 12:38 PM
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
/* Determine dates for various calendar events.*/
%DO YEAR=2011 %to 2020;
/* FDO-prefixed variables are First Day Of... For */
/* example, FDOY is First Day of 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. */
PRES_DAY=INTNX('WEEK.2',FDO_FEB,(WEEKDAY(FDO_FEB) NE 2)+2);
/* Memorial Day. */
MEM_DAY=INTNX('WEEK.2',FDO_MAY,(WEEKDAY(FDO_MAY) IN (1,7))+4);
/* Independence 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. */
LAB_DAY=INTNX('WEEK.2',FDO_SEP,(WEEKDAY(FDO_SEP) NE 2));
/* Columbus Day. */
COL_DAY=INTNX('WEEK.2',FDO_OCT,(WEEKDAY(FDO_OCT) NE 2)+1);
/* Election Day. */
/* Veterans Day. */
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. */
IF WEEKDAY(XMAS_DAY) = 1 THEN XMAS_HOL=XMAS_DAY+1;
IF WEEKDAY(XMAS_DAY) = 7 THEN XMAS_HOL=XMAS_DAY-1;
/* Write results to SAS Log. */
"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. / @@
09-17-2013 01:04 PM
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=);
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.;
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,
'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;
*get previous work day;
select max(date) format=date9. into rev_work_day from
date_dimension where workday=1 and date < &test_date;
09-17-2013 01:54 PM
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.
09-17-2013 02:11 PM
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.
09-17-2013 02:26 PM
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).
09-18-2013 09:07 AM
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));
/* define macro using recursion */
%if %index(&holidays,&check_day) or %index(17,%sysfunc(weekday(&check_day))) %then
/* 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.)));
09-19-2013 06:08 AM
I like the recursive solution of Amir very much. Very elegant!
But I have two comments.
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);