topic Re: How to call custom holiday calendar in the calculations in SAS Enterprise Guide

How to call custom holiday calendar in the calculations

sharath_rk — Wed, 02 Apr 2014 12:34:26 GMT

Hello,

I have created a custom holiday calender per below. I need the below logic to look up the holiday calendar also along with weekday to give the desired output. Please advise.

SELECT (REGION);

WHEN ('EMEA')

DO;

IF WEEKDAY(DATEPART(ORD_ENTRY_DATE)) NOT IN (1,7) & ORD_ENTRY_DATE not in EMEA_holidays_2014 then do c = 'made' ;

else c = 'miss';

END;

data EMEA_holidays_2014;

infile datalines truncover;

attrib

region informat=$5.

country informat=$25.

hol_date informat=date9. format=date9.;

input region Country hol_date;

datalines;

EMEA Russia 10MAR2014

EMEA Ukraine 10MAR2014

EMEA South_Africa 21MAR2014

EMEA Greece 25MAR2014

EMEA South_Africa 28MAR2014

EMEA Ireland 17APR2014

EMEA Slovakia 18APR2014

EMEA CPF 18APR2014

EMEA Switzerland 18APR2014

EMEA Portugal 18APR2014

EMEA Spain 18APR2014

EMEA EDB 18APR2014

EMEA South_Africa 18APR2014

EMEA Greece 18APR2014

EMEA UK 18APR2014

EMEA Italy 20APR2014

EMEA Hungary 21APR2014

EMEA UK 21APR2014

EMEA Greece 21APR2014

EMEA Switzerland 21APR2014

EMEA South_Africa 21APR2014

EMEA Austria 21APR2014

EMEA CPF 21APR2014

EMEA EDB 21APR2014

EMEA Ukraine 21APR2014

EMEA France 21APR2014

EMEA Slovakia 21APR2014

EMEA CzeSwitzerland_Repblic 21APR2014

EMEA Poland 21APR2014

EMEA Italy 21APR2014

EMEA Germany 21APR2014

EMEA Ireland 21APR2014

EMEA Portugal 24APR2014

EMEA Italy 25APR2014

EMEA South_Africa 25APR2014

EMEA South_Africa 28APR2014

EMEA Austria 01MAY2014

EMEA South_Africa 01MAY2014

EMEA Slovakia 01MAY2014

EMEA Portugal 01MAY2014

EMEA Poland 01MAY2014

EMEA Italy 01MAY2014

EMEA Hungary 01MAY2014

EMEA France 01MAY2014

EMEA Spain 01MAY2014

EMEA Germany 01MAY2014

EMEA CzeSwitzerland_Repblic 01MAY2014

EMEA Ireland 05MAY2014

EMEA UK 05MAY2014

EMEA EDB 05MAY2014

EMEA CPF 05MAY2014

EMEA Slovakia 08MAY2014

EMEA France 08MAY2014

EMEA CzeSwitzerland_Repblic 08MAY2014

;

run;

Re: How to call custom holiday calendar in the calculations

sharath_rk — Thu, 03 Apr 2014 15:10:34 GMT

Team,

Please advise whether it is possible to reference the custom calender?

Re: How to call custom holiday calendar in the calculations

cau83 — Thu, 03 Apr 2014 19:38:25 GMT

Can you be more specific about your question and/or problem?

Are you saying that the code you're trying isn't working, or you need additional code?

I think you may have a missing semi-colon but that's about all I can glean from your post:

SELECT (REGION);

WHEN ('EMEA')

DO;

IF WEEKDAY(DATEPART(ORD_ENTRY_DATE)) NOT IN (1,7) & ORD_ENTRY_DATE not in EMEA_holidays_2014 then do c = 'made' ;

else c = 'miss';

END;

In the 5th line there should be a semi-colon after 'do'.

Re: How to call custom holiday calendar in the calculations

nathan_owens — Thu, 03 Apr 2014 21:17:23 GMT

You can reference all of the holiday dates by created a macro variable that contains all of the dates.

proc sql noprint;

select distinct hol_date format=5. into :hol_list separated by ','

from EMEA_holidays_2014;

quit;

You can then look in the list with:

IF WEEKDAY(DATEPART(ORD_ENTRY_DATE)) NOT IN (1,7) & ORD_ENTRY_DATE not in (&hol_list) then c='made';

else c='miss';

Re: How to call custom holiday calendar in the calculations

Reeza — Thu, 03 Apr 2014 21:24:42 GMT

I don't think its quite that simple as the OP hasn't clearly explained his/her problem.

My guess, from previous posts, is that they have orders and delivery dates from various countries and need to see if deliveries are on time.

The holiday list for one country won't match up with others so it is more complicated than a simple if/then solution..

The original solution I posted to this issue, a date dimension, would still work and is fast. Unfortunately I don't have time to mock up a solution.

Other solutions posted include custom date interval creations, one per country. A mock up of that solution was provided in previous threads.

Re: How to call custom holiday calendar in the calculations

sharath_rk — Fri, 04 Apr 2014 06:38:30 GMT

i am not able to call EMEA_holidays_2014 variable in the calculation. Is there a way to do it? the below code is the format i am looking to look up the order date for weekends & emea holidays list.

SELECT (REGION);

WHEN ('EMEA')

DO;

IF WEEKDAY(DATEPART(ORD_ENTRY_DATE)) NOT IN (1,7) & ORD_ENTRY_DATE not in EMEA_holidays_2014 then do c = 'made' ;

else c = 'miss';

END;

Re: How to call custom holiday calendar in the calculations

Patrick — Fri, 04 Apr 2014 06:48:20 GMT

I believe your question relates to this thread https://communities.sas.com/thread/53847 and that the solution would be to create a custom calendar per country. So in the example I've posted in the referenced thread replace the region with the country and things should work.