Using a dynamic table name with month+year (mmyy) SAS EG

Question

Any help please? When displaying the variable vMonth, it is working but when concatenating it with the library name, the following issue is obtained.

Program:

%LET lastdaypreviousmonth = put(intnx('month', today(), -1, 'E'),mmyyn4.);
%LET vMonth = cats('RM',&lastdaypreviousmonth); 
PROC SQL;
SELECT &vMonth,*
FROM MASU.&vMonth
WHERE nsgr = '040';
QUIT;

Log file :

27         %LET lastdaypreviousmonth = put(intnx('month', today(), -1, 'E'),mmyyn4.);
28         %LET vMonth = cats('RM',&lastdaypreviousmonth);
29        
30         PROC SQL;
31        
32         SELECT &vMonth,*
33         FROM MASU.&vMonth
34         WHERE nsgr = '040';

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

NOTE: Line generated by the macro variable "VMONTH".

34          MASU.cats('RM',put(intnx('month', today(), -1, 'E'),mmyyn4.))

                          _                                    _

                          79                                   79

                                                               200

ERROR 79-322: Expecting a ).

 

ERROR 200-322: The symbol is not recognized and will be ignored.

Answer 1

The macro code is just doing what you told it to do. Add some %PUT statements to see what values you have put into your macro variables. The macro processer will not treat strings like put or cats any differently than it would treat the string xyz or 123.

If you want to call SAS functions in macro code you need to wrap each call with the %sysfunc() macro function. Not all functions can be called this way. In particular instead of the type flexible PUT() and INPUT() functions use the type specific versions instead. But in this case you can just use the format parameter of the %SYSFUNC() call instead of the function call. Do not include quotes in your string literals, everything is a string literal to the macro processor.

Use this:

%LET lastdaypreviousmonth=%sysfunc(intnx(month,%sysfunc(today()),-1, E),mmyyn4.);

There is no need to ever use the CAT...() functions in macro code. To concatenate macro variable value just expand them where you want them to appear.

%LET vMonth = RM&lastdaypreviousmonth.;