CODEWITHSUNDEEP
sas
SMW
SMW

Reputation: 502

INTNX function based off macro variable

Struggling to use INTNX feature for date logic. I have the following macro variable created -

%LET BDATE1 = '2015-07-12';*ACTIVITY BEGIN DATE;

I need to use this static date (whatever it may be at the time) and create an additional macro var. off of the condition of &BDATE1 + 7 ...attempting to resolve the value of '2015-07-19' so it can be passed in a query (RSUBMIT in a DB2 env.)

My attempt/assumption was that the following would give me what I needed to pass in the query -

DATA _NULL_;
%GLOBAL NEWDATE;
CALL SYMPUT('NEWDATE',PUT(INTNX('DAY',&BDATE1,+7),YYMMDD10.));
RUN;

This execution provides an error - 

NOTE: Invalid numeric data, '2015-07-12' , at line 1 column 1.
NOTE: Argument 2 to function INTNX('DAY',.,7) at line 979 column 27 is invalid.
_ERROR_=1 _N_=1

My assumption is that since I am creating &BDATE1 as a string date (for the DB2), the INTNX is reading this as a char. string, not a numeric value. Can anyone suggest another approach/resolution to this? I attempted to reformat the var, but the fact that it needs to be in string format causes an additional problem.

Upvotes: 2

Views: 2850

Answers (1)

Robert Penridge
Robert Penridge

Reputation: 8513

So the problem is because you are effectively running this code:

%LET BDATE1 = '2015-07-12';
data _null_;
  oops = INTNX('DAY',&BDATE1,+7);
run;

This is failing because the bdate1 macro variable resolves to a string, rather than a date literal. It's not resolving to a date literal because you forgot to specify the d suffix at the end of the string:

%LET BDATE1 = '2015-07-12'd;  /* HAS D SUFFIX */

That change should be sufficient to fix the code.

My personal preferred way of working with these kinds of requirements is to create a date value in a macro variable, and do the manipulations using %let statemnets.

%let bdate1 = %sysfunc(mdy(7,12,2015));
%let newdate = %sysfunc(intnx(day,&bdate1,7),yymmdd10.);

%put &newdate;

Gives:

2015-07-19

You can see the secret here is that the %sysfunc() function as a second parameter that allows you apply a format to the value to be returned.

EDIT : In case you are unfamiliar with the %sysfunc() function you are best off searching for some whitepapers on it. But basically, all it does is allows you to use functions that you would normally use in a data step, within the macro environment. In this case, we're using them within a %let statement.

I should also mention that your %global statement is not required unless that particular piece of code is executing within a %macro block. Any variables created outside of of macro, will effectively be global.

Upvotes: 3

Related Questions