Macro variable of second order in a proc sql

[sfmeier]

I am using a simple sql query with a macro variable in the FROM statement:

%LET vormonat = 201303;

%LET dynmonat = vormonat;

proc sql;

     create table &workbib..i_fdb2bereich as

            select distinct bp_bereich, bp_fdb_nr

            from lls_com._&&&dynmonat.._EGK_VB_GESAMT

            where bp_referenz_nr = 0; /* Berater für Basket auswählen */

quit;

1st step:

lls_com._&&&dynmonat.._EGK_VB_GESAMT

&& resolves to &

&dynmonat. resolves to vormonat

2nd step: &vormonat._EGK_VB_GESAMT

I get the error: 

45         create table &workbib..i_fdb2bereich as

46                select distinct bp_bereich, bp_fdb_nr

47                from lls_com._&&&dynmonat.._EGK_VB_GESAMT

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

47          lls_com._&Vormonat

                     _

                     22

                     76

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING,

              INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE. 

ERROR 76-322: Syntax error, statement will be ignored.

Whats wrong?

Thx, Stefan

[UrvishShah]

Hi,

There is extra dot in your FROM clause of Proc Sql...I have removed it below...Try it...And you don't need indirect reference of macro variables in this case...It simply runs with singal &

            FROM lls_com_&vormonat.._EGK_VB_GESAMT

-Urvish

[sfmeier]

Hello Urvish,

thank you for your reponse. I need the dot after lls_com because lls_com is the library. If course I dont need the indirect reference in the simplified code I posted, but the "%LET dynmonat = vormonat;" Statement is usually more complex. I just simplified it because it is not necessary to solve this problem. Any other ideas?

Stefan

[sfmeier]

Hi Tom,

thank you for your fast response and an important clue. Your ....

%put lls_com._&&&dynmonat.._EGK_VB_GESAMT;      (1)

... produces the following result in my code:

lls_com._&Vormonat._EGK_VB_GESAMT

So I think, the error comes from an earlier part of my code which you can see here:

%LET Stichtag = 20130131;

%LET Stichdatum = %sysfunc(INPUTN(&stichtag, B8601DA.));

%LET Vormonat = %sysfunc(INTNX(Month, &stichdatum, -1), YYMMN.);

The Macro variable VORMONAT seems correctly used:

SYMBOLGEN:  Macro variable VORMONAT resolves to 201301

Any ideas why my &Vormonat does not resolve to 201301 in (1)?

[sfmeier]

hi Tom,

btw: the

%unquote(lls_com._&&&dynmonat.._EGK_VB_GESAMT)

works!

but perhaps you could explain me why?

thank you,

Stefan

[Tom]

I do not know why, but sometimes SAS will see a token break in the middle of a word when you are generating them by pieces with macro logic.

So by wrapping it in an %unquote() the base SAS language parser will see the whole string as one token.

[sfmeier]

Oh.. ok.. thx!

[Tom · Accepted solution]

I have no trouble using this:

%LET vormonat = 201303;

%LET dynmonat = vormonat;

%put lls_com._&&&dynmonat.._EGK_VB_GESAMT;

Perhaps your actual macro variable values are quoted in some way that is confusing the SAS parser?

Try wrapping the table name with %UNQUOTE() .

%unquote(lls_com._&&&dynmonat.._EGK_VB_GESAMT)