SAS Create a matrix of holiday flags

Question

I would like to have a matrix of binary flags that indicate whether a holiday occurred on the given day, something like this (but with every SAS-recognized holiday):

date        flag_BOXING   flag_CHRISTMAS   flag_...   flag_...
14DEC2014      0                0             0           0
15DEC2014      0                0             0           0
16DEC2014      0                0             0           0
17DEC2014      0                0             0           0
18DEC2014      0                0             0           0
19DEC2014      0                0             0           0
20DEC2014      0                0             0           0
21DEC2014      0                0             0           0
22DEC2014      0                0             0           0
23DEC2014      0                0             0           0
24DEC2014      0                0             0           0
25DEC2014      0                1             0           0
26DEC2014      1                0             0           0
27DEC2014      0                0             0           0
28DEC2014      0                0             0           0

I know there is probably an easier way to do this, but wanted to make sure before I continue through with my current attempt (which does not actually work....). I was thinking of creating a separate column that brings in the date of the actual holiday, then if that date matches the day on the specific row, the flag variable becomes a 1. The downside to this is that I would have to do this for every holiday. Also, the holiday function requires a year specification, so I would have to use some if-elseif logic to account for different years (my list is several years of dates). Is there an easy way to produce the matrix I want or is this the best way to proceed?

data test;
    length day $9;
    input day $;
cards;
23DEC2014
24DEC2014
25DEC2014
26DEC2014
27DEC2014
28DEC2014
;
run;

data test(drop=BOXING CHRISTMAS);
    set test;
    BOXING=holiday('boxing', 2014);
    CHRISTMAS=holiday('christmas', 2014);
    format BOXING CHRISTMAS date9.;
    flag_BOXING=0;
    flag_CHRISTMAS=0;
    if upcase(day)=upcase(BOXING) then flag_BOXING=1;
    if upcase(day)=upcase(CHRISTMAS) then flag_CHRISTMAS=1;
run;

Thanks.

Answer 1

Here's a brute force method. If you only need specific dates, I'd do it this way and then merge/filter the results some way. Note that the arrays need to align, the holiday names and the flag variables for this to work. The h_list is a list of holidays of interest and is stored as a temporary array and not output to the final data set.

data want;
format date date9.;
array h_list(6) $12. _temporary_ ("newyear" "thanksgiving", "christmas", "easter", "mlk", "memorial");
array h_flag(6) flag_newyear flag_thanksgiving flag_christmas flag_easter flag_mlk flag_memorial;

date_start="01Jan2013"d;
date_end="31Dec2015"d;

do date=date_start to date_end;
year=year(date);

do i=1 to dim(h_list);
    h_flag(i)=0;
    if date=holiday(h_list(i), year) then h_flag(i)=1;
end;

output;
end;
run;

Answer 2

We have a macro to do just that. Just specify the date range that you want to calculate holidays for, and it will out a table, 1 row per holiday.

%holidays(iStartDt=%sysfunc(mdy(1,1,2007)), iEndDt=%sysfunc(today())+100);

You can take the dataset it creates and join to your existing data to get the fields you need.

Here's the macro:

/*****************************************************************************
**  PROGRAM: MACROS.HOLIDAYS.SAS
**
**  CREATES A DATASET CONTAINING A LIST OF PUBLIC HOLIDAYS
**  
**  PARAMETERS: 
**
******************************************************************************
**  HISTORY:
**  1.0 MODIFIED: 30-JUN-2010  BY:RP
**  - CREATED. 
*****************************************************************************/

%macro holidays(iStartDt=, iEndDt=);

  data holidays;
    attrib holiday_date format=date9.
           holiday_desc length=$30
           rule         length=$30
           ;

    yr_start = year(&iStartDt);
    yr_end   = year(&iEndDt);

    do yr_tmp=yr_start to yr_end;
      holiday_date = mdy(1,1,yr_tmp);
      holiday_desc = "New Years Day";
      rule         = "";
      output;
      holiday_date = mdy(7,4,yr_tmp);
      holiday_desc = "July 4th";
      rule         = "";
      output;
      holiday_date = mdy(11,11,yr_tmp);
      holiday_desc = "Veterans Day";
      rule         = "November 11";
      output;
      holiday_date = mdy(12,25,yr_tmp);
      holiday_desc = "Christmas Day";
      rule         = "";
      output;    

      **
      ** Martin Luther King Day    [3rd monday in Jan]
      *;
      cnt = 0;
      do tmp_date = mdy(1,1,yr_tmp) to mdy(2,1,yr_tmp);
        if weekday(tmp_date) eq 2 then do;
          cnt = cnt + 1;
          if cnt = 3 then do;
            leave;
          end;
        end;
      end;
      holiday_date = tmp_date;
      holiday_desc = "MLK Day";
      rule         = "3rd Monday in Jan";
      output;

      **
      ** Presidents Day [3rd monday in Feb]  
      *;
      cnt = 0;
      do tmp_date = mdy(2,1,yr_tmp) to mdy(3,1,yr_tmp);
        if weekday(tmp_date) eq 2 then do;
          cnt = cnt + 1;
          if cnt = 3 then do;
            leave;
          end;
        end;
      end;
      holiday_date = tmp_date;
      holiday_desc = "Presidents Day";
      rule         = "3rd Monday in Feb";
      output;

      **     
      ** Memorial Day [last monday in May]  
      *;
      cnt = 0;
      do tmp_date = mdy(6,1,yr_tmp)-1 to mdy(5,1,yr_tmp) by -1;
        if weekday(tmp_date) eq 2 then do;
          cnt = cnt + 1;
          if cnt = 1 then do;
            leave;
          end;
        end;
      end;
      holiday_date = tmp_date;
      holiday_desc = "Memorial Day";
      rule         = "Last monday in May";
      output;

      **
      ** Labor Day [1st monday in Sept]  
      *;
      cnt = 0;
      do tmp_date = mdy(9,1,yr_tmp) to mdy(10,1,yr_tmp);
        if weekday(tmp_date) eq 2 then do;
          cnt = cnt + 1;
          if cnt = 1 then do;
            leave;
          end;
        end;
      end;
      holiday_date = tmp_date;
      holiday_desc = "Labor Day";
      rule         = "1st monday in Sept";
      output;

      **
      ** Columbus Day [2nd monday in Oct]
      *;
      cnt = 0;
      do tmp_date = mdy(10,1,yr_tmp) to mdy(11,1,yr_tmp);
        if weekday(tmp_date) eq 2 then do;
          cnt = cnt + 1;
          if cnt = 2 then do;
            leave;
          end;
        end;
      end;
      holiday_date = tmp_date;
      holiday_desc = "Columbus Day";
      rule         = "2nd monday in Oct";
      output;

      ** 
      ** Thanksgiving Day  [4th thursday in Nov]
      *;
      cnt = 0;
      do tmp_date = mdy(11,1,yr_tmp) to mdy(12,1,yr_tmp);
        if weekday(tmp_date) eq 5 then do;
          cnt = cnt + 1;
          if cnt = 4 then do;
            leave;
          end;
        end;
      end;
      holiday_date = tmp_date;
      holiday_desc = "Thanksgiving Day";
      rule         = "4th thursday in Nov";
      output;

    end;

    keep holiday_date holiday_desc rule;

  run;

  proc sort data=holidays(where=(holiday_date between &iStartDt and &iEndDt));
    by holiday_date;
  run;

  **
  ** WHEN A HOLIDAY FALLS ON A NONWORKDAY -- SATURDAY OR SUNDAY -- THE HOLIDAY USUALLY IS OBSERVED 
  ** ON MONDAY (IF THE HOLIDAY FALLS ON SUNDAY) OR FRIDAY (IF THE HOLIDAY FALLS ON SATURDAY).
  *;
  data holidays;
    format adjusted_date date9.;
    length adjusted_downame downame $15;
    set holidays;

    downame = upcase(cats(put(holiday_date, downame.)));
    if downame eq ("SATURDAY") then do;
      adjusted_date = holiday_date - 1;
    end;
    else if downame eq ("SUNDAY") then do;
      adjusted_date = holiday_date + 1;
    end;
    else do;
      adjusted_date = holiday_date;
    end;
    adjusted_downame = upcase(cats(put(adjusted_date, downame.)));

  run;
%mend;