Desktop productivity for business analysts and programmers

How to call custom holiday calendar in the calculations

Reply
Contributor
Posts: 51

How to call custom holiday calendar in the calculations

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;

Contributor
Posts: 51

Re: How to call custom holiday calendar in the calculations

Team,

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

Contributor
Posts: 69

Re: How to call custom holiday calendar in the calculations

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

Contributor
Posts: 51

Re: How to call custom holiday calendar in the calculations

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;

Respected Advisor
Posts: 3,823

Re: How to call custom holiday calendar in the calculations

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.

Occasional Contributor
Posts: 12

Re: How to call custom holiday calendar in the calculations

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';

Grand Advisor
Posts: 17,313

Re: How to call custom holiday calendar in the calculations

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.

Ask a Question
Discussion stats
  • 6 replies
  • 351 views
  • 0 likes
  • 5 in conversation