topic Re: Creating a Macro Variable, but format is incorrect. in SAS Programming

Creating a Macro Variable, but format is incorrect.

tonywu — Tue, 11 May 2021 00:45:04 GMT

Hi community,

I have come across following error when I was trying to create a macro variable.

Hope you guys could help in this case.

We are currently using MONTH_1 where we need to change the input each month.

So I tried to create MONTH so that it will automatically run last month data.

However the problem is that MONTH_1 is character, MONTH will be numeric.

I tried <<<%let MONTH = %sysfunc(put(%sysfunc(intnx(month, %sysfunc(today()), -3), yymmn6.), $.);>>>, but still getting the warning that "VARIABLE HAS ALREADY BEEN DEFINED AS NUMERIC".

Thanks and regards,

/*Macro Variables*/
%let MONTH_1 = '202104';
%let MONTH = %sysfunc(intnx(month, %sysfunc(today()), -3), yymmn6.);
/*%let NOW = %sysfunc(today(), yymmn6.);*/

data _null_;
call symput('BEG_DATE',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),-12,'b'),yymmn6.));
call symput('RCMS_DATE',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),-13,'b'),yymmn6.));
call symput('EXTRACT',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),0,'e'),yymmn6.));
call symput('EXT_DATE',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),0,'e'),date9.));
call symput('TAB_DATE',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),-84,'b'),yymmn6.));
call symput('LAST_PERF_DATE',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),-18,'e'),date9.)); *Allowing 18 mths outcome window;
call symput('LAST_PERF_MTH',put(intnx('month',input(put(&MONTH.,$6.), yymmn6.),-18,'e'),yymmn6.)); *Allowing 18 mths outcome window;
run;
%put &BEG_DATE. &RCMS_DATE. &EXTRACT. &EXT_DATE. &TAB_DATE. &LAST_PERF_DATE. &LAST_PERF_MTH. &MONTH &MONTH_1;

Re: Creating a Macro Variable, but format is incorrect.

Tom — Tue, 11 May 2021 02:57:21 GMT

Where is the error message? Show the SAS log lines for the data step that is generating the error.

Macro variables contain text. So this code

%let MONTH = %sysfunc(intnx(month, %sysfunc(today()), -3), yymmn6.);

will set MONTH to a string of digits that is 6 characters long. Like 202101.

If you use it to generate SAS code like:

newvar= &month ;

Then NEWVAR will be a numeric variable since you ran this statement:

newvar = 202101 ;

which will set NEWVAR to the number 202,101.

But you set MONTH_1 to string of digits enclosed in quotes. So if you coded

newvar = &month_1 ;

Then NEWVAR will be a character variable since you ran this code:

newvar = '202104' ;

Re: Creating a Macro Variable, but format is incorrect.

Patrick — Tue, 11 May 2021 03:03:23 GMT

In your data _null_ step you expect macro variable &month to resolve to a string of digits which you then convert to a numerical SAS date value using the input function. Because it's a string you need to have &month in double quotes.

/*Macro Variables*/
%let MONTH_1 = '202104';
%let MONTH = %sysfunc(intnx(month, %sysfunc(today()), -3), yymmn6.);

/*%let NOW = %sysfunc(today(), yymmn6.);*/
data _null_;
  call symput('BEG_DATE',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),-12,'b'),yymmn6.));
  call symput('RCMS_DATE',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),-13,'b'),yymmn6.));
  call symput('EXTRACT',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),0,'e'),yymmn6.));
  call symput('EXT_DATE',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),0,'e'),date9.));
  call symput('TAB_DATE',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),-84,'b'),yymmn6.));
  call symput('LAST_PERF_DATE',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),-18,'e'),date9.));
  *Allowing 18 mths outcome window;
  call symput('LAST_PERF_MTH',put(intnx('month',input(put("&MONTH.",$6.), yymmn6.),-18,'e'),yymmn6.));
  *Allowing 18 mths outcome window;
run;

%put &=BEG_DATE.;
%put &=RCMS_DATE.;
%put &=EXTRACT.;
%put &=EXT_DATE.;
%put &=TAB_DATE.;
%put &=LAST_PERF_DATE.;
%put &=LAST_PERF_MTH.;
%put &=MONTH.;
%put &=MONTH_1.;

Re: Creating a Macro Variable, but format is incorrect.

Tom — Tue, 11 May 2021 03:31:37 GMT

If you want to replicate what you did before just add the quotes:

%let MONTH = '202104';
%let MONTH = "%sysfunc(intnx(month, %sysfunc(today()), -3), yymmn6.)";

It is probably worth it to cut down on the number of nested function calls to make your code easier to decipher.


%let MONTH = "%sysfunc(intnx(month, %sysfunc(today()), -3), yymmn6.)";
data _null_;
*  Convert macro variable into date ;
  month = input(&month,yymmn6.);
  * 1 year ;
  BEG_DATE = intnx('month',month,-12);
  * 13 months ;
  RCMS_DATE = intnx('month',month,-13);
  * end of month ;
  EXT_DATE= intnx('month',month,0,'e');
  * 7 years ;
  TAB_DATE= intnx('month',month,-84);
  * 18 months ;
  LAST_PERF_DATE = intnx('month',month,-18,'e');

* Generate macro variables ;
  call symputx('BEG_DATE',put(BEG_DATE,yymmn6.));
  call symputx('RCMS_DATE',put(RCMS_DATE,yymmn6.));

  call symputx('EXTRACT',put(EXT_DATE,yymmn6.));
  call symputx('EXT_DATE',put(EXT_DATE,date9.));

  call symputx('TAB_DATE',put(TAB_DATE,yymmn6.));

  call symputx('LAST_PERF_DATE',put(LAST_PERF_DATE,date9.)); 
  call symputx('LAST_PERF_MTH',put(LAST_PERF_DATE,yymmn6.)); 
run;

Results:

182   %put &=MONTH ;
MONTH="202102"
183   %put &=BEG_DATE. &=RCMS_DATE. &=EXTRACT.;
BEG_DATE=202002 RCMS_DATE=202001 EXTRACT=202102
184   %put &=EXT_DATE. &=TAB_DATE. &=LAST_PERF_DATE. &=LAST_PERF_MTH.;
EXT_DATE=28FEB2021 TAB_DATE=201402 LAST_PERF_DATE=31AUG2019 LAST_PERF_MTH=201908

Re: Creating a Macro Variable, but format is incorrect.

tonywu — Tue, 11 May 2021 03:48:56 GMT

yeah, it's not really an error but a warning.

I have some other codes after that call up this variable to search our folder to see if xxxx_202104 file would exist.

That's where the error comes.

Re: Creating a Macro Variable, but format is incorrect.

tonywu — Tue, 11 May 2021 03:50:02 GMT

this one actually worked.

it is showing the date in double quotations, where the previous one is in single quotation.

But I assume this would work for the following codes?