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;
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.
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).
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.
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;
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.
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
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.
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.
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
John and why is my testcode not generating those blanks as your code is. What is different?
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?
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.
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ó
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.
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() ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.