topic Re: Help Me find the error in the macro! in New SAS User

Help Me find the error in the macro!

d6k5d3 — Tue, 08 Jan 2019 09:31:03 GMT

The following macro gives me errors like: Invalid date/time/datetime constant '01JAN&i.'d.

%macro domestic_holidays (year1, year2);

data aaa;
set HUF;

%do i= &year1. %to &year2.;

if Date in ('01JAN&i.'d, '15MAR&i.'d, '08APR&i.'d, '01MAY&i.'d,
		'20AUG&i.'d, '23OCT&i.'d, '01NOV&i.'d, '25DEC&i.'d,
			'26DEC&i.'d) then delete;
%end;
run;

%mend domestic_holidays;

%domestic_holidays (2007, 2018);

However, when I run the code without macro, it runs fine:

data aaa;
set HUF;

if Date in ('01JAN2007'd, '15MAR2007'd, '08APR2007'd, '01MAY2007'd,
			'20AUG2007'd, '23OCT2007'd, '01NOV2007'd, '25DEC2007'd,
			'26DEC2007'd) then delete;
if Date in ('01JAN2008'd, '15MAR2008'd, '08APR2008'd, '01MAY2008'd,
			'20AUG2008'd, '23OCT2008'd, '01NOV2008'd, '25DEC2008'd,
			'26DEC2008'd) then delete;
run;

Where's the error in the macro?

Much thanks!

Re: Help Me find the error in the macro!

Kurt_Bremser — Tue, 08 Jan 2019 09:43:52 GMT

Single quotes prevent the resolution of macro triggers. Use double quotes instead.

Re: Help Me find the error in the macro!

novinosrin — Tue, 08 Jan 2019 09:47:24 GMT

Macro list and statement genertion is not an ideal design. Rather have a look up table and update the look up table when needed

Re: Help Me find the error in the macro!

Reeza — Tue, 08 Jan 2019 16:17:57 GMT

It looks like the day and month are always the same, but the year changes. Are you trying to implement this for specific years?
If so, you can avoid macro logic entirely.

length ddmon $5.;

if year(date) in (2007, 2008, 2009) then do;

ddmon = put(date, date9.);

if ddmon in ('01JAN', '15MAR', '08APR' ...) then delete;

end;

@d6k5d3 wrote:

The following macro gives me errors like: Invalid date/time/datetime constant '01JAN&i.'d.

%macro domestic_holidays (year1, year2);

data aaa;
set HUF;

%do i= &year1. %to &year2.;

if Date in ('01JAN&i.'d, '15MAR&i.'d, '08APR&i.'d, '01MAY&i.'d,
		'20AUG&i.'d, '23OCT&i.'d, '01NOV&i.'d, '25DEC&i.'d,
			'26DEC&i.'d) then delete;
%end;
run;

%mend domestic_holidays;

%domestic_holidays (2007, 2018);

However, when I run the code without macro, it runs fine:

data aaa;
set HUF;

if Date in ('01JAN2007'd, '15MAR2007'd, '08APR2007'd, '01MAY2007'd,
			'20AUG2007'd, '23OCT2007'd, '01NOV2007'd, '25DEC2007'd,
			'26DEC2007'd) then delete;
if Date in ('01JAN2008'd, '15MAR2008'd, '08APR2008'd, '01MAY2008'd,
			'20AUG2008'd, '23OCT2008'd, '01NOV2008'd, '25DEC2008'd,
			'26DEC2008'd) then delete;
run;

Where's the error in the macro?

Much thanks!

Re: Help Me find the error in the macro!

Tom — Tue, 08 Jan 2019 16:46:01 GMT

You need to use enclose strings in double quotes if you want the macro processor to operate on the values. The single quotes prevent the macro processor from checking the string.

"01JAN&i"d

Here is updated/simplified macro that does not need to worry about that since it eliminated the %DO loop.

%macro domestic_holidays (year1, year2);

data aaa;
set HUF;

if (&year1 <= year(date) <= &year2) and
  put(date,date9.) in: ('01JAN' '15MAR' '08APR' '01MAY' '20AUG' '23OCT' '01NOV' '25DEC' '26DEC') 
then delete;

run;

%mend domestic_holidays;

%domestic_holidays (2007, 2018);

But I suspect that to really do this right you would need to adjust the holiday schedule for each year to account for what day of the week the dates fall on. In that case you might be better off using a dataset of holidays.

With such a dataset your macro for generating the data step might look more like this.

%macro domestic_holidays (year1, year2);
%local dt_list ;
proc sql noprint;
  select date into :dt_list separated by ' '
  from holiday_schedule
  where year between &year1 and &year2
  ;
quit;

data aaa;
  set HUF;
  where date not in (&dt_list);
run;

%mend domestic_holidays;

%domestic_holidays (2007, 2018);