Calling INTNX from %sysfunc

[ScottBass]

Hi,

I'm sure I'll feel like an idiot once I get the answer but, hey, cheap and easy points to the first correct answer 😉

I have to convert this expression:

where clmDt < max(intnx('week1.6',(today()-1),0), intnx('month',today(),-1,'e'));

into "pure" macro so the SPDE engine can optimize the where clause.  Plus it's really bad form to use functions in a where clause if the value never changes.  Finally, for reasons dealing with macros and code generation, I can't just call this data step at the beginning of my program.

I've tried this code:

* test code ;

data _null_;
  x=max(intnx('week1.6',(today()-1),0), intnx('month',today(),-1,'e'));
  call symputx("where",x);
run;

%let where=clmDt < &where;
%put &where;

* pure macro equivalent? ;

%let today=%sysfunc(today());
%let friday=%sysfunc(intnx('week1.6',(&today-1),0));
%let eom=%sysfunc(intnx('month',&today,-1,'e'));
%let max=%sysfunc(max(&friday,&eom));
%let where=clmDt < &max;
%put &where;

but the macro code barfs on the calls to intnx.

Trying something simpler:

data _null_;
  x=intnx('year',0,0,'E');
  put x= x=date9.;
run;

%put %sysfunc(intnx('year',0,0,'E'));

still fails.

The oh-so-helpful error message is:

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.

Which argument to INTNX is out of range?  'year', 0, 0, or 'E'?

Any ideas???

Thanks,

Scott

[art297]

Scott,

Just after you first datastep you have the following line:

%let where=clmDt < &where;

Where does clmDt come from?  It doesn't appear to be part of a datastep.

[ScottBass]

Hi Art,

The where clause criteria is stored in a metadata file (Excel worksheet), and a macro uses the metadata to generate a "big SAS job".

The "big SAS job" (not mentioned in my original post) subsets the fact table on:

where clmDt <= max( intnx('week1.6',(today()-1),0) , intnx('month',today(),-1,'e'));

I need to convert that to the equivalent number, without using the functions in the where clause.  So, on today, 23Sep11, that would be:

where clmDt <= 18886;

Later in the "big SAS job", there is the line:

where &where;

which does the subsetting.

I know, the easy thing is to just call the data _null_ code before the beginning of my job.  But, because of the code generation process mentioned above, it will slot into our current framework easier if I can use a "pure macro" solution to add this to the job metadata.

HTH...

Scott

[SASJedi · Accepted solution]

Scott,

Take the quotations out of your macro code and it will work fine:

 * pure macro equivalent? ;
%let today=%sysfunc(today());
%let friday=%sysfunc(intnx(week1.6,(&today-1),0));
%let eom=%sysfunc(intnx(month,&today,-1,e));
%let max=%sysfunc(max(&friday,&eom));
%let where=clmDt < &max;
%put &where;

[ScottBass]

Well, don't I feel like an idiot ;-).  I should have thought of that, and I've done this very thing in other macros in the past.

My brain isn't working too well this Friday!

Thanks!

[JatinRai]

Awesome response SASJedi. You saved me several minutes. But why do we remove the quotes? What is the logic or thumb rule applicable here when the original syntax uses quotes. I'm interested to know the general principle behind this because as Scott says that he has used this in other macros as well. So where all am I expected to remove the quotes?

[SASJedi]

Glad to be able to help 🙂  When calling DATA step function from MACRO using %SYSFUNC, the general rule is to always leave out the quotes.  Macro doesn't use quotes to mark text like the DATA step does, and even though you are calling a DATA step function, the processing is in MACRO, not DATA step, so the quotes will usually just mess things up.

I wish I could tell you this was a 100% hard and fast rule for all occasions, but I hate to lie... 🙂  I know I've had to insert quotes for the argument of a DATA step function on some odd, rare occasion - but I just can't for the life of me remember what it was right now.  Just remember: always try it WITHOUT the quotes first - that way, most everything will work the first time you try it without issues.

[JatinRai]

Thanks for your prompt reply. You're awesome!

[ScottBass]

(I don't seem to have the links to mark this Answered or give you the Correct Answer points...I'm using Opera on the Mac if that matters...)

Edit:  Yep, the website doesn't have complete functionality when using Opera.  Firefox worked...

Message was edited by: Scott Bass