DATA Step, Macro, Functions and more

Macro variable of second order in a proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Macro variable of second order in a proc sql

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


Accepted Solutions
Solution
‎05-14-2013 07:08 AM
Super User
Super User
Posts: 7,078

Re: Macro variable of second order in a proc sql

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)

View solution in original post


All Replies
Regular Contributor
Posts: 195

Re: Macro variable of second order in a proc sql

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

Contributor
Posts: 53

Re: Macro variable of second order in a proc sql

Posted in reply to UrvishShah

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


Contributor
Posts: 53

Re: Macro variable of second order in a proc sql

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)?

Contributor
Posts: 53

Re: Macro variable of second order in a proc sql

hi Tom,

btw: the

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

works!

but perhaps you could explain me why?

thank you,

Stefan

Super User
Super User
Posts: 7,078

Re: Macro variable of second order in a proc sql

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.

Contributor
Posts: 53

Re: Macro variable of second order in a proc sql

Oh.. ok.. thx!

Solution
‎05-14-2013 07:08 AM
Super User
Super User
Posts: 7,078

Re: Macro variable of second order in a proc sql

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)

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 835 views
  • 4 likes
  • 3 in conversation