BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

17 REPLIES 17
ballardw
Super User

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.

 

 

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

 

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

 

Tom
Super User Tom
Super User

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.

 

ScottBass
Rhodochrosite | Level 12

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

 

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Tom
Super User Tom
Super User

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.

 

Astounding
PROC Star

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.

ScottBass
Rhodochrosite | Level 12

&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!)


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Astounding
PROC Star

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.

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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;

ballardw
Super User

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.

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9
Date5 is always the March of the year prior to date1, where date6 is always the March of two years prior to date1.
ballardw
Super User

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));
Tom
Super User Tom
Super User

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.

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9
Thanks everyone so much for all the advices!! I tried the below approach and it worked.
%LET YEAR = 2016;
%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 = %sysfunc(MDY(3,31,&YEAR-1));
%LET DATE6 = %sysfunc(INTNX(MONTH,&DATE5,-12,E));

DATA TEST;
SET WAREHOUSE;
WHERE AS_OF_DATE IN (&DATE, &DATE1,&DATE2, &DATE3, &DATE4, &DATE5, &DATE6);
RUN;

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!

How to Concatenate Values

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.

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
  • 17 replies
  • 5189 views
  • 7 likes
  • 5 in conversation