BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bentleyj1
Quartz | Level 8

I'm using the macro facility to generate SQL that is passed to Oracle for execution and I'm using the macro variable &&_uniqDbTable&_n holds Oracle table names.  So far so good.

I use &&_uniqDbTable&_n in the query's FROM clause to conditionally specify how to Join the tables involved.  I've used macro variables with double-ampersands before but never in this particular way.  It works but with some strange behavior.  Here we see the double-ampersand macro variable table name separated by two dots from the field name,

 

%if &_nUniqTables >= 1 %then %do _n=1 %to &_nUniqTables;

     and base.clnt_num=&&_uniqDbTable&_n..clnt_nr

     and trim(base.loan_num)=&&_uniqDbTable&_n..ln_nr

%end;

My experience is that two dots as a delimiter following a macro variable resolves as one dot, and I use that construct frequently to specify a table.fieldname such as &_table1..&_field1 -- this will resolve properly as customer_info.customer_name.

The problem with the code above is that two dots following the double-ampersand macro variable resolves as a blank and so throws an error (see the attachment).  One dot as a delimiter resolves as no delimiter (as expected), and three dots resolves to a blank followed by a dot.  Four dots resolves to blank-dot-dot   The query works with blank-dot to separate table and field so I can live with that but I'd sure like to understand why it resolves like that.

The attached log snippet shows the entire query and Error messages for one, two, three and four dots at the delimiter.  I haven't asked SAS Support about it yet.  Can anyone explain what's going on?

I'm using explicit SQL pass-thru via execute ( <query> ) by oracle;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Extra blanks in the value of the macro variables will cause trouble. But I have definitely seen certain combinations where there are not extra spaces and the parser still gets confused. Adding extra code such as %unquote() or changing the code will fix the confusion.

View solution in original post

17 REPLIES 17
Tom
Super User Tom
Super User

Just add another dot.

The reason for using extra dots is that the dot has special meaning to the macro processor.  You can use it to indicate the end of the macro variable name.  For example if you want to use a macro variable to hold the beginning of the name and then make the rest of the name be hard coded.  The dot lets the macro processor know that the other characters are not part of the macro variable name.

%let basename=age ;

...

&basename._squared = &basename ** 2;

...

So when you wanted variable X to hold the database (or schema) name you would write code like this to reference table A in that database.

&X..A.

But since you are using double && it will mean that SAS will process the macro reference twice.  So it will "eat" two of the dots. And that means that you no longer have the constant dot that is required to generate a two level table name.

Test it using some simple values and the %PUT statement.

53   %let db=MYSCHEMA;

54   %let db1=MYSCHEMA;

55   %let i=1;

56   %put &DB.. ;

MYSCHEMA.

57   %put &Db&I.. ;

MYSCHEMA1.

58   %put &&DB&I.. ;

MYSCHEMA

59   %put &&DB&I... ;

MYSCHEMA.

Or turn on symbolgen option and see what the macro processor is doing (and clutter up your log so you can't tell what actual SAS is doing).

bentleyj1
Quartz | Level 8

Thanks for the quick response Tom.  I'm familiar with how the macro processor handles delimiters and that's why I don't understand the behavior I'm seeing.

When used with a single ampersand macro variable two dots resolve as a single dot.   But the attachment shows that when used with a double-ampersand macro variable two dots resolve to a blank and three dots resolve to blank-dot.  I'm wondering why.

Tom
Super User Tom
Super User

Sounds like you are getting snagged by some parsing issue.

You could try %UNQUOTE() around the reference.

Normally I avoid these type of "macro array" solutions, but when I use them generate the expanded value into a new macro variable. I find it much easier to read.

%if &_nUniqTables >= 1 %then %do _n=1 %to &_nUniqTables;

  %let table= &&_uniqDbTable&_n ;

     and base.clnt_num=&table..clnt_nr

     and trim(base.loan_num)=&table..ln_nr

%end;

test_user_name
Calcite | Level 5

You might have to rethink your concept of delimiters.

&& resolves into &

But two dots resolve into two dots.  Always.  The confusion arises when it looks like you have two dots, but one of them is text and one of them is a delimiter.  Take a piece of your troublesome code:

&&_uniqDbTable&_n.clnt_nr

Macro language treats this as 4 pieces of text:

&& _uniqDbTable &_n. clnt_nr

It resolves each, coming up with:

&_uniqueDbTable1clnt_nr

This is probably not what you are hoping for.  If you wanted it to resolve to:

&_uniqueDbTable1.clnt_nr

Then Tom's solution is 100% correct ... just add another dot:

&&_uniqDbTable&_n..clnt_nr

Now the pieces of text are:

&& _uniqDbTable &_n. .clnt_nr

The first dot is a delimiter, delimiting that the name of the macro variable is _n.  So this resolves to:

&_uniqDbTable1.clnt_nr

The second dot remained as text, but now becomes a delimiter for the name of the macro variable _uniqDbTable1.

There are a number of possible variations for what you might be hoping for.  Perhaps a third dot would be required here.  But clarifying how dots/delimiters work is the key issue.

bentleyj1
Quartz | Level 8

Thanks for the detailed reply.  The attachment shows the results when using 1 to 4 dots as delimiters between the macro variable holding the table name and the actual field name.  When I say I use two dots as delimiters I recognize that the first serves a delimiter marking the end of the macro variable name and the second separates the (resolved) database table name from the field name.

My question is why are 3 dot delimiters required when working with a double-ampersand macro variable holding the table name?  Why do the first 2 of the 3 resolve into a space?  We see also this behavior when 4 dots are used.

In my code when &_n=1 then &&_uniqDbTable&_n resolves to &_uniqDbTable1 and that resolves to V_HM_CE_CUST_ATTR.

The attachement shows the following--

Number of dot delimiters      Usage                                        Resolution                                       Result

     1                                   &&_uniqDbTable&_n.clnt_nr          &_uniqDbTable1clnt_nr                   Error, invalid character

     2                                   &&_uniqDbTable&_n..clnt_nr         V_HM_CE_CUST_ATTR clnt_nr      Error, SQL not properly ended

     3                                   &&_uniqDbTable&_n...clnt_nr        V_HM_CE_CUST_ATTR .clnt_nr     no error, SQL runs OK

     4                                   &&_uniqDbTable&_n....clnt_nr       V_HM_CE_CUST_ATTR ..clnt_nr    Error, invalid table.column    

 

Tom
Super User Tom
Super User

It is not that they are resolving to have an extra space. It is that the notation is confusing the macro processor and it is treating the .TABLENAME as a separate token EVEN THOUGH THERE IS NO SPACE.  If you try the same code outside of a macro (do it for a single value of the loop iteration variable) the generated code will work.  But of course to iterate over the list of macro variables you have to be in a macro.

nravi
Calcite | Level 5

1 When the macro has four dots, the order of resolution is:

2 &&_uniqDbTable&_n....clnt_nr

3 &_uniqDbTable1...clnt_nr (break down: two && to one & and 4 dots to 3 dots)

4 V_HM_CE_CUST_ATTR ..clnt_nr (break down: one & is dropped and 1 dot is dropped) - so result is two dots

5 When the macro has 3 dots, the order of resolution is:

6 &&_uniqDbTable&_n...clnt_nr

7 &_uniqDbTable1..clnt_nr (break down: two && to one & and 3 dots to 2 dots)

8 V_HM_CE_CUST_ATTR.clnt_nr (one & is dropped) -  so result is one dot

Note: numbers on 1st position on each line designates line number.

Observe that in line 3 when 3 dots are present, there is only one &, but, in line 6 when 3 dots are present, there are two &.

Hope, that makes sense.

bentleyj1
Quartz | Level 8

Yes, your explanation makes sense nravi.  But it doesn't explain why my log shows that two dots resolved to a blank and three dots resolved to blank-dot.

It happens each time I run the code.  The macro value containing the table name does not contain a trailing space.

John

jakarman
Barite | Level 11

John and why is my testcode not generating those blanks as your code is. What is different?

---->-- ja karman --<-----
gergely_batho
SAS Employee

Are you sure it doesn't contain a trailing space? It could be the only explanation (that I can think of).

Put this line into your code:

%put XXX&_uniqDbTable1.XXX;

Is there a blank?

Tom
Super User Tom
Super User

It is not resolving to include a blank. If you try it outside of a macro you will see that it works fine.  Instead the syntax is confusing the macro processor and it is treating the result as two separate tokens, even though there is really no spaces between them.

That is why wrapping the expansion inside of %unquote() solves the problem.  This makes the token parser realize that it needs to wait until the %UNQUOTE() was executed before it can determine the number and boundaries of the tokens being generated inside the %unquote() function.

gergely_batho
SAS Employee

Tom,

I still believe, that there is a trailing blank in the macro variable.

Your code at Sep 25, 2014 1:24 PM is working, because the %let statement removes trailing blanks.

%unquote() has similar powers. In this example %unquote removes trailin blanks:

%macro x();

proc sql noprint; select 'class ' into :tab1 from sashelp.class;quit;

%do i=1 %to 1;

%put %unquote(&&tab&i..).age;

%end;

%mend;

%x();

I also ran Jaap's test. No blanks are added.

bentleyj1, can you try puting this to the log?

It is "easy" to get trailing spaces into macro variables if you use proc sql into, or data step call symput.

Thx

Message was edited by: Gergely Bathó

Tom
Super User Tom
Super User

Extra blanks in the value of the macro variables will cause trouble. But I have definitely seen certain combinations where there are not extra spaces and the parser still gets confused. Adding extra code such as %unquote() or changing the code will fix the confusion.

jakarman
Barite | Level 11

I made a small macro that should have the same logic.

Running that with the 3 dots as should be it give a nice expansion without that space.

All messages are not clear whether this is as space in the marcro variable being resolved or not. It is space on a space.

You could try to trim trailing spaces SAS(R) 9.4 Macro Language: Reference, Second Edition   

When there is a space in that macrovar (the second expansion) the follow question would be how did that happen?  

options mprint symbolgen mlogic source2 ;

%macro ntstr () ;

%let tab1=oraloan ;

%let tab2=orabent ;

%do _n=1 %to 2 ;

   %put ... and trim(base.loan_num)=&&tab&_n...lnr   ;

%end;

%mend;

%ntstr() ;


---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 3604 views
  • 6 likes
  • 6 in conversation