Hi All,
I am trying to define three different dates shown as below for the current month (August), previous month (July) and previous two months (June) in the macro statments. It seems worked. However, when I put all of them in the where statement inside the data step below, I got the syntax error. Can anyone advise on this? Thanks.
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a numeric constant, a datetime constant, a missing value, ), -.
ERROR 76-322: Syntax error, statement will be ignored.
%LET DATE = '31AUG2016'D;
%LET DATE1 = INTNX('MONTH',&DATE,-1,'E'); /* JULY */
%LET DATE2 = INTNX('MONTH',&DATE,-2,'E'); /*JUNE*/
DATA TEST;
SET WAREHOUSE;
WHERE AS_OF_DATE IN (&DATE, &DATE1, &DATE2);
RUN;
One of the nice features of %SYSFUNC() is that you don't need to add %EVAL() for the function arguments.
You can call %sysfunc(mdy(3,31,2015-1)) the same way you can call mdy(3,31,2015-1) in a normal SAS code.
Your macro variables are TEXT.
So when you use
%LET DATE1 = INTNX('MONTH',&DATE,-1,'E'); /* JULY */
%LET DATE2 = INTNX('MONTH',&DATE,-2,'E'); /*JUNE*/
your Where clause looks like this:
WHERE AS_OF_DATE IN ('31AUG2016'D, INTNX('MONTH',&DATE,-1,'E'), INTNX('MONTH',&DATE,-1,'E'));
For this purpose you want:
%LET DATE1 = %sysfunc(INTNX('MONTH',&DATE,-1,'E')); /* JULY */
%LET DATE2 = %sysfunc(INTNX('MONTH',&DATE,-2,'E')); /*JUNE*/
to RESOLVE the value of the function INTNX. The %sysfunc is used to resolve data step functions in macros code or values.
Thanks Ballardw. I added the %sysfunc but i still didn't get July and June data. I got the warning message as below:
Do you have any ideas? Thanks
%LET DATE = '31AUG2016'D;
16 %LET DATE1 = %sysfunc(INTNX('MONTH',&DATE,-1,'E'));
WARNING: An argument to the function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set
to a missing value.
17
Do not add quotes around string literals in macro code. The INTNX() function is complaining that is doesn't know anything about a time interval named 'MONTH'. It is looking for MONTH or YEAR or DAY etc.
%LET DATE = '31AUG2016'D;
%LET DATE_number = %sysfunc(INTNX(MONTH,&DATE,-1,E));
%LET DATE_string = %sysfunc(INTNX(MONTH,&DATE,-1,E),DATE9);
%LET DATE_literal = "%sysfunc(INTNX(MONTH,&DATE,-1,E),DATE9)"D;
%put &=date_number ;
%put &=date_string ;
%put &=date_literal ;
You can use the values of &DATE_NUMBER or &DATE_LITERAL inter changably in your code. But if you want to use &DATE_STRING to mean a date then you need to use it in the format "&DATE_STRING"D to convert it to a date literal.
%LET DATE = '31AUG2016'D;
%LET DATE1 = INTNX('MONTH',&DATE,-1,'E'); /* JULY */
%LET DATE2 = INTNX('MONTH',&DATE,-2,'E'); /*JUNE*/
DATA TEST;
SET WAREHOUSE;
WHERE AS_OF_DATE IN (&DATE, &DATE1, &DATE2);
RUN;
This is strictly a matter of personal coding style, but the style I've developed over the years is:
1) I don't embed "syntax" in my macro variables. I want my macro variables to only contain data.
2) For macro variables containing date, time, or datetime, I prefer stroring them as date/time/datetime literals. It often makes the log look better, such as the echoing of the where clause in the data step above.
3) Even with SQL Server passthrough, where date literals must be single-quoted, I use either:
A) WHERE DATE BETWEEN %bquote('&startdate') and %bquote('&enddate'); or
B) %let q=%str(%'); WHERE DATE BETWEEN &q&startdate&q and &q&enddate&q;
I used to use B), then a colleague put me onto the %bquote() macro function. Now I think A looks better, even with the extra keystrokes - I just think it looks clearer.
So, this then becomes:
%LET DATE = 31AUG2016;
* or even %LET DATE = %sysfunc(INTNX(MONTH,"12AUG2016"d,0,E),date9.); * to ensure the EOM ;
%LET DATE1 = %sysfunc(INTNX(MONTH,"&DATE"d,-1,E),date9.); /* JULY */
%LET DATE2 = %sysfunc(INTNX(MONTH,"&DATE"d,-2,E),date9.); /*JUNE*/
%PUT &=DATE &=DATE1 &=DATE2; * for debugging ;
DATA TEST;
SET WAREHOUSE;
WHERE AS_OF_DATE IN ("&DATE"d, "&DATE1"d, "&DATE2"d);
RUN;
Run that and see how the log looks cleaner, with the date literals displayed in the where clause, instead of some number which represents the date.
See the doc for the %sysfunc function and the use of the optional <format> to format the output.
HTH...
You need to remove the quotes around MONTH and E when calling INTNX within %SYSFUNC() macro function.
String literals are not quoted in macro code.
Since you're trying to process the data with DATA step functions, it might be easier to read if you used a DATA step:
%let date = '31aug2016'd;
data _null_;
call symputx('date1', intnx('month', &date, -1, 'E'));
call symputx('date2', intnx('month', &date, -2, 'E'));
run;
Then the SQL code can remain as is.
&date1 would then be something like 12345 and &date2 something like 12372, or whatever number of days after 01JAN1960 represent those dates.
If you %put those values to the log, it would be difficult to know if you'd generated the correct result.
If you used those values in a where clause, the SAS notes would use those values, rather than the date literals, again making it difficult to know if you've selected the correct records.
It's just personal preference...I just prefer the literals.
So, even if you use a data step, this then becomes:
data _null_;
call symputx('date1', put(intnx('month', &date, -1, 'E'),date9.);
call symputx('date2', put(intnx('month', &date, -2, 'E'),date9.);
run;
I personally don't find this easier to read than the use of %sysfunc, but that's just a matter of coding style. Both approaches work.
(Kudos for using symputx - as far as I'm concerned symput is deprecated!)
Guilty as charged, but this is mostly a matter of style ... I might use your suggestion for QC of a program, but I would rely on the program to work if the right numbers were in there. Depending on what the program does, it might be apparent (from the reports it produces) whether the right dates were being selected.
Note that if you do convert the DATA step as you suggested (and assuming you balance the parentheses), you have to change the SQL code. You can no longer refer to &DATE1 (and similarly &DATE2), but have to switch to "&DATE1"d.
Thanks everybody here! The codes below worked perfectly now!
Now I am trying to automate date5 and date6, which would be the March end of the previous two years(as always).
I wonder if there's any way to automate it?
Really being gladly apperciated for everyone's helps here.
%LET DATE = '31AUG2016'D;
%LET DATE1 = %sysfunc(INTNX(MONTH,&DATE,-1,E));
%LET DATE2 = %sysfunc(INTNX(QTR,&DATE,-1,E));
%LET DATE3 = %sysfunc(INTNX(QTR,&DATE,-2,E));
%LET DATE4 = %sysfunc(INTNX(MONTH,&DATE,-12,E));
%LET DATE5 = '31MAR2015'D;
%LET DATE6 = '31MAR2014'D;
DATA TEST;
SET WAREHOUSE;
WHERE AS_OF_DATE IN (&DATE, &DATE1,&DATE2, &DATE3, &DATE4, &DATE5, &DATE6);
RUN;
Is DATE5 always going to be March of the year prior to Date1? or the fifth month prior to date1 and then the previous year?
You already know how to get Date6 from Date5, use intnx with Year instead of Month.
You really want to move this stuff to a data step and create the macro variables with call symputx. You have to nest so many function calls, each of which calling for an explicit %sysfunc or calculation calling for an %eval or %sysevalf that the code gets ugly and hard to maintain.
data example;
DATE1 = '31AUG2016'D;
date5 = mdy(3,31,Year(date)-1);
date6 = intnx('Year',date5,-1,'S');
format date date5 date6 date9.;
/* call symputx statements left as an exercise for
the interested reader*/
run;
%let date1 = '31AUG2016'D;
%let date5 = %sysfunc(mdy(3,31,%eval(%sysfunc(year(&date1)) -1))) ;
%let date6 = %sysfunc(intnx(YEAR,&date5,-1,S));
One of the nice features of %SYSFUNC() is that you don't need to add %EVAL() for the function arguments.
You can call %sysfunc(mdy(3,31,2015-1)) the same way you can call mdy(3,31,2015-1) in a normal SAS code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.