topic Re: Trying to do mathmatical calculation on macro variable dates in New SAS User

Trying to do mathmatical calculation on macro variable dates

robulon — Wed, 05 Dec 2018 17:28:07 GMT

Hi,

I'm trying to write some code that I need to run to backdate some reports. I want to compare data at one point to six months later.

I have created macro variables in the code like this: -

%let query_month = '01feb2018'd;

%let datalimit = %sysfunc(intnx(day,%sysfunc(today()),-1),date9.);

*Create macro variables;

data _null_;

month = intnx('month',&query_month.,0);

call symputx("file_month",put(intnx('month',month, 0), monyy5.),'G');

call symputx("start_file",put(intnx('month',month,-1,'e'), yymmddn8.),'G');

call symputx("start_date",put(intnx('month',month,-1,'e'), date9.),'G');

call symputx("end_file_1m",put(intnx('month',month,0,'e'), yymmddn8.),'G');

call symputx("end_date_1m",put(intnx('month',month,0,'e'), date9.),'G');

call symputx("end_file_6m",put(intnx('month',month,5,'e'), yymmddn8.),'G');

call symputx("end_date_6m",put(intnx('month',month,5,'e'), date9.),'G');

run;

The rest of my program is a macro program. Halfway into it, I then have some code that compares the &end_date_6m date with the &datalimit date and only want the next bit of code to run if the &end_date_6m is prior to &datalimit, as the data will not exist otherwise.

The log shows that the macro variables are being resolved as I would expect but SAS doesn't read one date as being earlier than the other

%if "&end_date_6m."d < "&datalimit."d %then %do;

SYMBOLGEN: Macro variable END_DATE_6M resolves to 31JUL2018

SYMBOLGEN: Macro variable DATALIMIT resolves to 04DEC2018

MLOGIC(ROLL_RATES): %IF condition "&end_date_6m."d < "&datalimit."d is FALSE

MLOGIC(ROLL_RATES): Ending execution.

I'm sure this must be something to do with the macro variables being resolved as character rather then the numeric values but could anyone advise me what I need to do to correct this?

As always, many thanks in advance.

Rob

Re: Trying to do mathmatical calculation on macro variable dates

Reeza — Wed, 05 Dec 2018 17:36:41 GMT

Try wrapping it in %SYSEVALF().

%let end_date_6m = 31JUL2018;
%let datalimit= 04DEC2018;



%if %sysevalf("&end_date_6m."d < "&datalimit."d) %then %do;

	%put ERROR- Testing complete;

%end;

This worked fine for me:

338 %let end_date_6m = 31JUL2018;
339 %let datalimit= 04DEC2018;
340
341
342
343 %if %sysevalf("&end_date_6m."d < "&datalimit."d) %then %do;
344
345 %put ERROR- Testing complete;
Testing complete
346
347 %end;

Re: Trying to do mathmatical calculation on macro variable dates

PaigeMiller — Wed, 05 Dec 2018 17:41:12 GMT

Macro variable dates must be actual integers (SAS date values), otherwise the macro processor cannot work with them.

Remove the FORMATting and the PUT function from your CALL SYMPUTX, like this

call symputx("end_date_6m",intnx('month',month,5,'e'),'G');

Of course, the idea of doing mathematical calculations with macro variables, other than integer arithmetic, is not recommended. It's possible, but you have to work harder, as shown by @Reeza.

Re: Trying to do mathmatical calculation on macro variable dates

robulon — Wed, 05 Dec 2018 18:14:57 GMT

Thanks both, you guys are awesome!