BookmarkSubscribeRSS Feed
sharath_rk
Calcite | Level 5

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;

6 REPLIES 6
sharath_rk
Calcite | Level 5

Team,

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

cau83
Pyrite | Level 9

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

sharath_rk
Calcite | Level 5

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;

Patrick
Opal | Level 21

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.

nathan_owens
Obsidian | Level 7

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

Reeza
Super User

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.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1152 views
  • 0 likes
  • 5 in conversation