How to get a SAS variable into a Macro?

[Phil_Low]

Well this didn't work.

/*Macro for determining holidays*/
%macro Holiday_MCR;
 /*holiday counter*/
 
%let holiday_count=0;

%do i=%sysfunc(year(&start_date.)) %to %sysfunc(year(&end_date.));
  %if &start_date.<=%sysfunc(holiday(NEWYEAR,&i.)) and %sysfunc(holiday(NEWYEAR,&i.))<= &end_date.  %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(USINDEPENDENCE,&i.))and %sysfunc(holiday(USINDEPENDENCE,&i.))<= &end_date.  %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(MLK,&i.)) and %sysfunc(holiday(MLK,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(USPRESIDENTS,&i.)) and %sysfunc(holiday(USPRESIDENTS,&i.))<= &end_date.  %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(COLUMBUS,&i.)) and %sysfunc(holiday(COLUMBUS,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(VETERANS,&i.)) and %sysfunc(holiday(VETERANS,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(THANKSGIVING,&i.)) and %sysfunc(holiday(THANKSGIVING,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(CHRISTMAS,&i.)) and %sysfunc(holiday(CHRISTMAS,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(MEMORIAL,&i.)) and %sysfunc(holiday(MEMORIAL,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
  %if &start_date.<=%sysfunc(holiday(LABOR,&i.)) and %sysfunc(holiday(LABOR,&i.))<= &end_date. %then %let Holiday_count=%eval(&Holiday_count.+1);
%end;
%put Holiday count is &holiday_count.;
&holiday_count.
%mend;

data test;

begin_date=today()-365;
finish_date=today();

call symputx('start_date',begin_date);
call symputx('end_date',finish_date);

days_diff = intck('WEEKDAY', begin_date, finish_date)-%holiday_mcr.; 

run;

I'm trying to calculate the number of holidays between two date ranges and subtract that from the calculation to get actual working days.  However the twist is this must be done with a macro as I am going to be applying it to a lot of different variables.

[PaigeMiller]

Assuming the logic in your macro is correct (I haven't tried to check that), you SAS code is wrong. You need to take out this line

%put Holiday count is &holiday_count.;

and then it might work. Of course, you should really show us the LOG, so we can be sure we are diagnosing the error properly. So, please run the command

options mprint symbolgen mlogic;

and then run your code again, and provide the log by clicking on the {i} icon (DO NOT SKIP THIS STEP) and then pasting the ENTIRE log (not just the error messages, but the entire log) into the window that appears. For future reference, saying "well that didn't work" is never enough information, when code doesn't run properly, we need to see the log.

Also, I disagree that this needs to be done in a macro, it would be much simpler in a data step.

[Phil_Low]

As I mentioned I have to apply then to many different variables in many different datasets.  It would literally require 20 copy and pastes or more to do it purely in a datastep, so I'm trying to get a macro together than I can just call when needed.

[Tom]

---

@Phil_Low wrote:

As I mentioned I have to apply then to many different variables in many different datasets.  It would literally require 20 copy and pastes or more to do it purely in a datastep, so I'm trying to get a macro together than I can just call when needed.

---

Show the SAS code that you want the macro to create.  Explain what part of it needs to change.  If it is just the dataset name and the variable name then converting it to a macro is trivial.

So if the code looks like:

data want ;
  set have;
  ....  varname .... 
run;

Then the macro becomes:

%macro mymacro(have,want,varname);
data &want ;
  set &have;
  ....  &varname .... 
run;
%mend mymacro;

[Tom]

I don't understand how you are proposing to use macro code here.

Normally to apply the same process to multiple variables in the same dataset you would use an ARRAY, not macro code.

Show what works for one variable without any macro code at all. Then you can evaluate if you need a macro at all. 

[Phil_Low]

I have 10 years of SAS experience under my belt, please everyone assume I know what I am talking about when I say I need a macro solution.  I have to apply this solution across many datasets and I don't want to copy and paste all over. If you don't want to assist that is fine, I understand. 

I need to somehow get datastep date variables into the macro and then get the calculated macro variable produced back into the days_diff calculation.

[Phil_Low]

Here is the working code that has not been turned into a macro.

/*Second holiday counter to deal with CME completed date below*/
holiday_count=0;
if cme_completed_date>. then do i=year(datepart(CME_COMPLETED_DATE)) to year(today()-1);
if datepart(CME_COMPLETED_DATE)<=holiday('NEWYEAR',i)<=today()-1  then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('USINDEPENDENCE',i)<=today()-1  then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('MLK',i)<=today()-1 then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('USPRESIDENTS',i)<=today()-1  then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('COLUMBUS',i)<=today()-1 then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('VETERANS',i)<=today()-1 then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('THANKSGIVING',i)<=today()-1 then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('CHRISTMAS',i)<=today()-1 then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('MEMORIAL',i)<=today()-1 then holiday_count+1;
if datepart(CME_COMPLETED_DATE)<=holiday('LABOR',i)<=today()-1 then holiday_count+1;
end;

if intck('WEEKDAY', datepart(cme_completed_date), today()-1)-holiday_count>89 then delete; 

[Tom]

So what is it need help with?  

Are you trying to accumulate HOLIDAY_COUNT over multiple observations?  If so why is it reset to zero on every observation. If not why are you using the SUM statement?

[Phil_Low]

it's set to 0 at the start of every observation.  the holiday counter do loop then counts all the holidays between the start dat and then end date.  the final count of all holidays is then subtracted from the final tally of weekdays in the intck function to get actual working days for that observation.

When the next observation begins, the holiday count is set back to 0.

[Phil_Low]

Everyone,  I apologize, my code works just fine.  I realize the problem.  At the bottom of my original post there is a . after the Macro invocation %holiday_mcr   the . was causing my issues

[Tom]

Your syntax was confusing the parser and it is not seeing the period as part of the number being generated by the macro call.

Example:

%macro xx; 123 %mend;
data test;
  x=200-%xx.;
  put x=;
run;

If you really needed that extra period you could add %UNQUOTE() around the whole token.

data test;
  x=200-%unquote(%xx.);
  put x=;
run;

[Reeza]

Another option, especially if this is something that is done regularly is to build a custom calendar and use INTNX()/INTCK() instead. This is the cleanest solution overall that would be the most efficient.

If you'd like to use that approach, which is a single one liner then, please indicate so and I can post a solution though it may not be until later tonight (it's on my home computer, not work computer).

It's also documented here:
https://communities.sas.com/t5/SAS-Communities-Library/SAS-Tip-Generating-Holiday-Lists/ta-p/557799?...

Another approach I used 15 years ago, which still works, is to create a date dimension table (borrowed from snowflake structure) and join on that table and then sum the workdays. Calculation of a date dimension is illustrated here:
https://gist.github.com/statgeek/9606118

[Tom]

So what do you want the macro to do?  Is it this simple:

%macro holiday_count(varname);
holiday_count=0;
if not missing(&varname) then do i=year(datepart(&varname)) to year(today()-1);
if datepart(&varname)<=holiday('NEWYEAR',i)<=today()-1  then holiday_count+1;
if datepart(&varname)<=holiday('USINDEPENDENCE',i)<=today()-1  then holiday_count+1;
if datepart(&varname)<=holiday('MLK',i)<=today()-1 then holiday_count+1;
if datepart(&varname)<=holiday('USPRESIDENTS',i)<=today()-1  then holiday_count+1;
if datepart(&varname)<=holiday('COLUMBUS',i)<=today()-1 then holiday_count+1;
if datepart(&varname)<=holiday('VETERANS',i)<=today()-1 then holiday_count+1;
if datepart(&varname)<=holiday('THANKSGIVING',i)<=today()-1 then holiday_count+1;
if datepart(&varname)<=holiday('CHRISTMAS',i)<=today()-1 then holiday_count+1;
if datepart(&varname)<=holiday('MEMORIAL',i)<=today()-1 then holiday_count+1;
if datepart(&varname)<=holiday('LABOR',i)<=today()-1 then holiday_count+1;
end;
total_days = intck('WEEKDAY', datepart(&varname), today()-1)-holiday_count ;
%mend holiday_count;

data want;
  set have ;
  %holiday_count(cme_completed_date);
  if total_days > 89 then delete;
run;

[ballardw]

To get separate counts for multiple dates with two arrays, one for the dates, one to hold the counts.

array dt  CME_COMPLETED_DATE otherdt1 otherdt2;
array h  holiday_count otherholcount1 otherholcount2;

Do j=1 to dim(dt);
   if dt[j]>. then do i=year(datepart(CME_COMPLETED_DATE)) to year(today()-1);
      if datepart(dt[j])<=holiday('NEWYEAR',i)<=today()-1  then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('USINDEPENDENCE',i)<=today()-1  then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('MLK',i)<=today()-1 then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('USPRESIDENTS',i)<=today()-1  then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('COLUMBUS',i)<=today()-1 then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('VETERANS',i)<=today()-1 then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('THANKSGIVING',i)<=today()-1 then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('CHRISTMAS',i)<=today()-1 then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('MEMORIAL',i)<=today()-1 then h[j] = sum(h[j],1);
      if datepart(dt[j])<=holiday('LABOR',i)<=today()-1 then h[j] = sum(h[j],1);
   end;
end;

if you need the total of all of the counts then

totalcount = sum(of h(*));

after that block.

No macro needed. Just add the datetime variables to the first array and a corresponding holiday counter variable to the second.

Likely would drop the holiday counter variables for the output data set unless you see a use.

And if this were my code I would likely include a temporary array to hold the names of the holidays of interest with another loop to loop over the holidays. That would simplify the code if I needed to change the holidays of interest for some reason.

And you had to use the reset of your holiday counter because the:

holiday_count+1 ;

creates a Retained variable.