topic Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter in SAS Programming

Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

bentleyj1 — Thu, 25 Sep 2014 16:31:17 GMT

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;

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

Tom — Thu, 25 Sep 2014 16:48:57 GMT

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

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

bentleyj1 — Thu, 25 Sep 2014 17:03:19 GMT

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.

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

Tom — Thu, 25 Sep 2014 17:24:21 GMT

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;

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

test_user_name — Thu, 25 Sep 2014 17:34:09 GMT

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.

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

bentleyj1 — Thu, 25 Sep 2014 18:14:18 GMT

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

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

Tom — Thu, 25 Sep 2014 18:38:05 GMT

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.

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

jakarman — Thu, 25 Sep 2014 18:44:37 GMT

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() ;

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

nravi — Sat, 27 Sep 2014 06:13:56 GMT

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.

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

bentleyj1 — Mon, 29 Sep 2014 14:57:24 GMT

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

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

jakarman — Mon, 29 Sep 2014 15:00:05 GMT

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

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

gergely_batho — Mon, 29 Sep 2014 15:09:33 GMT

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?

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

Tom — Mon, 29 Sep 2014 15:16:01 GMT

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.

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

gergely_batho — Mon, 29 Sep 2014 15:51:50 GMT

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ó

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

Tom — Mon, 29 Sep 2014 17:21:48 GMT

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.

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

bentleyj1 — Mon, 29 Sep 2014 19:04:05 GMT

Very curious. I'd sworn there were no trailing blanks in the macro value and I was wrong.

Earlier in the code I created the macro variable with this code figuring it would remove trailing blanks. The maximum length of tableName is 30 characters and the length of _uniqTable1 was 30 when the trailing blanks are counted.

%** Get the unique table names. ;

select unique strip(tableName) label='Unique Table Name'

into : _uniqDbTable1

%if &_nUniqTables > 1 %then -

: _uniqDbTable&_nUniqTables ;

from &project_name._cell_ltr_tables;

So why does %let _table= &&_uniqDbTable&_n remove trailing blanks but the Proc SQL syntax didn't?

If this is a feature of Proc SQL then I'm going to have to be more careful going forward because I use it a lot to create macro variables.

Thanks to all who contributed.

John

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

Tom — Mon, 29 Sep 2014 19:13:36 GMT

%LET removes the spaces because spaces are interpreted as token delimiters. So %let X= xxxx ; is the same as %let X=xxxx;

You do not need that macro logic. You can let PROC SQL take care of knowing how many macro variables to make. If you have a newer version of SAS then just do not put anything after the hyphen. If you have an older version then just use some obviously too large value for the upper bound. SAS will only create the number of macro variables that it needs. It will also count the number of values for you.

%** Get the unique table names. ;

select unique tableName

into :_uniqDbTable1 - :_uniqDbTable9999

from &project_name._cell_ltr_tables

;

%let _nUniqTables = &sqlobs ;

Re: Strange behavior when double-ampersand macro variable needs a multiple dot delimiter

jakarman — Mon, 29 Sep 2014 19:29:57 GMT

John there is a difference in macro language parsing and sas language parsing. Gergely Bathó already mentioned that.
With the datastep and proc sql you can put literally every kind of character into a macro variable

SAS(R) 9.4 SQL Procedure User's Guide (using macro vars) the trim is explicitly activated with concatenating the content. As it mentioned is not by default.

for the datastep there is new function SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition (symputx) is trims space and let you control wich symboltable you are using. Be carefull on that one too as local tables can or may not be nested.

The macro quoting (SAS(R) 9.4 Macro Language: Reference, Second Edition - SAS(R) 9.4 Macro Language: Reference, Second Edition (%quote an %unquote) are to be used when you are defining character in a macro-variable that can conflict with the macro-language. Very predictable and constant in behavior.

As rule of thumb: when possible conflicting chars can occur use %nrquote at the moment of defining and %unquote when using.