topic Macro variable of second order in a proc sql in SAS Programming

Macro variable of second order in a proc sql

sfmeier — Tue, 14 May 2013 09:13:57 GMT

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

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

Re: Macro variable of second order in a proc sql

UrvishShah — Tue, 14 May 2013 10:46:23 GMT

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

Re: Macro variable of second order in a proc sql

Tom — Tue, 14 May 2013 11:08:51 GMT

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)

Re: Macro variable of second order in a proc sql

sfmeier — Tue, 14 May 2013 11:28:40 GMT

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

Re: Macro variable of second order in a proc sql

sfmeier — Tue, 14 May 2013 11:53:31 GMT

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

Re: Macro variable of second order in a proc sql

sfmeier — Tue, 14 May 2013 12:02:59 GMT

hi Tom,

btw: the

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

works!

but perhaps you could explain me why?

thank you,

Stefan

Re: Macro variable of second order in a proc sql

Tom — Tue, 14 May 2013 13:12:33 GMT

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.

Re: Macro variable of second order in a proc sql

sfmeier — Tue, 14 May 2013 13:20:27 GMT

Oh.. ok.. thx!