topic Re: Intnx function in SAS Programming

Intnx function

LL5 — Fri, 09 Sep 2016 13:49:18 GMT

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;

Re: Intnx function

ballardw — Fri, 09 Sep 2016 14:30:20 GMT

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.

Re: Intnx function

LL5 — Fri, 09 Sep 2016 14:45:26 GMT

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.

Re: Intnx function

ScottBass — Fri, 09 Sep 2016 16:14:37 GMT

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

Re: Intnx function

Tom — Fri, 09 Sep 2016 15:37:12 GMT

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.

Re: Intnx function

Astounding — Fri, 09 Sep 2016 15:45:04 GMT

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.

Re: Intnx function

Tom — Fri, 09 Sep 2016 15:46:36 GMT

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.

Re: Intnx function

ScottBass — Fri, 09 Sep 2016 16:40:30 GMT

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

Re: Intnx function

Astounding — Fri, 09 Sep 2016 16:36:19 GMT

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.

Re: Intnx function

LL5 — Fri, 09 Sep 2016 18:41:25 GMT

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;

Re: Intnx function

ballardw — Fri, 09 Sep 2016 19:47:53 GMT

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.

Re: Intnx function

LL5 — Fri, 09 Sep 2016 21:29:27 GMT

Date5 is always the March of the year prior to date1, where date6 is always the March of two years prior to date1.

Re: Intnx function

ballardw — Fri, 09 Sep 2016 21:53:10 GMT

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));

Re: Intnx function

Tom — Fri, 09 Sep 2016 21:58:14 GMT

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.

Re: Intnx function

LL5 — Fri, 09 Sep 2016 22:30:31 GMT

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;

Re: Intnx function

LL5 — Fri, 09 Sep 2016 22:32:33 GMT

And also, I used this in Proc Sql, it worked as well.

Re: Intnx function

ScottBass — Mon, 12 Sep 2016 09:19:11 GMT

@LL5 wrote:
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;

As often with SAS (and Perl), "there is more than one way to do it" (tm)

Now that this has evolved to six macro variables (and perhaps more in the future?), perhaps a non-macro approach could be useful?

The "real" problem here is selecting records matching key dates. The macro code was just to derive those dates. This can be done in many ways without the use of macro:

First create your desired dates (however you choose to do so):

data dates;
   start='31AUG2016'd;  * or today(), or whatever seed value you need ;
   date=intnx('month',start,0,'E');output;
   date=intnx('qtr',start,-2,'E');output;
   date=mdy(31,3,2016-1);output;
   date=mdy(1,4,2016-2)-1;output;
   * etc, etc, ;  * create a dataset containing the dates you want ;
run;

Then, you can subset your data with:

* merge and IN= (but this requires the source and lookup data to be sorted)

* proc sql inner join (but this will sort the source data under the covers)

* proc sql nested query (proc sql; create table test as select * from warehouse where date in (select date from dates) )

* create a format from your dates, then: (data test; set warehouse; if not missing(put(date,mydatefmt.)); run; )

* use a hash object for your dates, and check the return code from the hash.lookup() function

* create an indexed dataset for your dates, and use a lookup ("double set statement"), and check _iorc_

None of these approaches would use macro, and might be easier to modify if your needs change in the future.

If your WAREHOUSE dataset is large you want to avoid sorting. Use the _method option on proc sql to see if it sorted your source table or not.

Hope this helps...

Re: Intnx function

LL5 — Mon, 12 Sep 2016 13:35:33 GMT

Thanks ScottBass for letting me know the other approaches to accomplish this. Since the data in warehouse is very large which contains lots of observations and variables, I used Proc Sql followed by lots of Proc Tabulate statements to get the end result. I will keep in mind for all the different approaches that you mentioned above and may come up with additional questions in the future. Thanks 🙂