DATA Step, Macro, Functions and more

Intnx function

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 44
Accepted Solution

Intnx function

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;


Accepted Solutions
Solution
‎09-09-2016 08:27 PM
Super User
Super User
Posts: 7,039

Re: Intnx function

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


All Replies
Super User
Posts: 11,343

Re: Intnx function

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.

 

 

Contributor LL5
Contributor
Posts: 44

Re: Intnx function

 

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

 

Super User
Super User
Posts: 7,039

Re: Intnx function

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.

 

Super Contributor
Posts: 387

Re: Intnx function

[ Edited ]

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

 

 

Super User
Super User
Posts: 7,039

Re: Intnx function

Posted in reply to ScottBass

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.

 

Super User
Posts: 5,497

Re: Intnx function

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.

Super Contributor
Posts: 387

Re: Intnx function

[ Edited ]
Posted in reply to Astounding

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

Super User
Posts: 5,497

Re: Intnx function

Posted in reply to ScottBass

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.

Contributor LL5
Contributor
Posts: 44

Re: Intnx function

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;

Super User
Posts: 11,343

Re: Intnx function

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.

Contributor LL5
Contributor
Posts: 44

Re: Intnx function

Date5 is always the March of the year prior to date1, where date6 is always the March of two years prior to date1.
Super User
Posts: 11,343

Re: Intnx function

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));
Solution
‎09-09-2016 08:27 PM
Super User
Super User
Posts: 7,039

Re: Intnx function

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.

Contributor LL5
Contributor
Posts: 44

Re: Intnx function

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 838 views
  • 7 likes
  • 5 in conversation