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

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


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.
1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users

View solution in original post

8 REPLIES 8
art297
Opal | Level 21

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
Rhodochrosite | Level 12

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


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.
SASJedi
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users
ScottBass
Rhodochrosite | Level 12

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!


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.
JatinRai
Obsidian | Level 7

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
SAS Super FREQ

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.

Check out my Jedi SAS Tricks for SAS Users
JatinRai
Obsidian | Level 7

Thanks for your prompt reply. You're awesome!

ScottBass
Rhodochrosite | Level 12

(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


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.

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
  • 8 replies
  • 49533 views
  • 13 likes
  • 4 in conversation