DATA Step, Macro, Functions and more

dynamic column name in proc sql with macro variable

Accepted Solution Solved
Reply
Regular Contributor
Posts: 210
Accepted Solution

dynamic column name in proc sql with macro variable

[ Edited ]

I tend to use a lot of dynamic (explicit) sql in poc sql. Something like this dynamic WHERE CLAUSE works fine :

 

WHERE TableAlias.ColumnName IN (%bquote('&MacroVariable.'))

However for some reason, this snippet of a SQL query:

 

SELECT &MacroVariableContainingColumnName. FROM ...

 

does not work. I get the error message:

 

Table does not contain MacroVariableContainingColumnName

 

So the actual content of 

 

(e.g. Column1) is not used.

 

 

Just to clarify, I want to generate the dynamic SQL:

 

select C1

 

from something along those lines:

 

%let MacroVariableContainingColumnName = C1;

using

proc sql;
		connect to TERADATA (OVERRIDE_RESP_LEN=YES SERVER=DBC AUTHDOMAIN="Bla" MODE=TERADATA);
		create table X.Y as
		    Select *
		    from connection to teradata
		    (
				SELECT &MacroVariableContainingColumnName. 
...

 

Any ideas?


Accepted Solutions
Solution
‎10-14-2017 03:26 AM
Super User
Super User
Posts: 8,289

Re: dynamic column name in proc sql with macro variable

[ Edited ]
Posted in reply to csetzkorn

This message:

Table does not contain MacroVariableContainingColumnName

Makes it sound like your have not defined your macro variable.  When the SAS macro processor sees what looks like a macro variable reference such as &XX and the macro variable does not exist then it just ignores it and passes the &XX on to SAS do deal with.  So in this case it looks like that happened and the result was that your table does not have a variable with that name.  Make sure you have defined your macro variable before using it.

 

Note that SAS names cannot be longer than 32 characters and that name is 33 characters. So that cannot be used for either a macro variable name,  a variable name or a dataset member name.

 

Perhaps you are trying to use the & as a trigger for some type of "dynamic" SQL in passthru code?  In that case you should try masking the & so that the SAS macro processor doesn't try to interpret it.

 

proc sql ;
connect to .... as MYDB ;
execute (
.... %nrstr(&something_or_other) ....
) by MYDB;
quit;

View solution in original post


All Replies
Super User
Super User
Posts: 9,866

Re: dynamic column name in proc sql with macro variable

Posted in reply to csetzkorn

Hi,

 

Firstly, there is rarely a use for dynamic SQL, SQL much like most data processing languages assumes you, the user and owner of the data, know your data.  If you don't then coding is pointless.

 

Second, posting a question and stating it does not work does not give us any information at all.  By the Post button when starting a new question you will see some guidance for what constitutes a good question, and from most post you will see this re-iterated.  Post test data in the form of a datastep in the body of the post using the code window ({i}), show what code you are using, also this code should show and macro's or macro variables used.  Also a good idea to show what you want out.

 

For the two tiny bits of code you use:

WHERE TableAlias.ColumnName IN (%bquote('&MacroVariable.'))

Why is this not:

WHERE TableAlias.ColumnName IN "&MacroVariable."

 And this:

SELECT &MacroVariableContainingColumnName. FROM ...

Seems perfectly fine, but depends on what the macro variable actually contains. 

Respected Advisor
Posts: 3,293

Re: dynamic column name in proc sql with macro variable

[ Edited ]

RW9 wrote:

Hi,

 

Firstly, there is rarely a use for dynamic SQL, SQL much like most data processing languages assumes you, the user and owner of the data, know your data.  If you don't then coding is pointless.


Using macro variables in SQL makes perfect sense to me. Suppose you have to write a system where users can request a specific analysis be performed on whatever columns of the database they want to use? Today they want to analyze columns A, B and C used in the analysis, and tomorrow they can request to analyze columns F,G,S and U. And so on.

 

But I agree with everyone, the original poster needs to provide his example and code.

--
Paige Miller
Super User
Super User
Posts: 9,866

Re: dynamic column name in proc sql with macro variable

Posted in reply to PaigeMiller

Sorry, maybe not clear.  SQL has no concept of dynamic code, you select the variables from the tables and apply logic.  The only variance comes from * which is a bit frowned upon as it shows a lack of knowledge of the data.  

So forget the SQL part, and focus on the code generation from SAS macro language.  I.e. the code generated from that is invalid.  Why this is, well likely due to lack of knowledge of the data, true its useful to have people select the columns they want, but those variables need to exist and do what they are supposed to, just slapping any old text in there will not work.  It maybe that this comes from a UI where a user selects columns from a list of valid ones, if so then that part is fine, and maybe he just has a delimiter issue within the macro variable - this we cant tell from the "it doesn't work" detailed description.

Regular Contributor
Posts: 210

Re: dynamic column name in proc sql with macro variable

SQL cannot be dynamic? To use dynamic sql (text) is a common practice - although it can cause sql injection but this is another story. I know the underlying data but in one analysis I want to look at column1 and another at column2 etc. The macro variable contains the 'dynamic' column names ...
Respected Advisor
Posts: 3,293

Re: dynamic column name in proc sql with macro variable

You keep talking about lack of knowledge of the data, when there are plenty of applications where the exact opposite is true.

 

I wrote an application where the engineers could submit a text file to a specific folder, and the text file contained information about which variables to analyze. NO lack of knowledge of the data here, the engineers provided the application with the exact variables names they wanted to analyze. 

 

And so macro variables in PROC SQL in a SELECT statement is the perfect way to program this.

--
Paige Miller
Regular Contributor
Posts: 210

Re: dynamic column name in proc sql with macro variable

Thanks. I really do not get the point of your reply - as others have also eluded to. IN (%bquote('&MacroVariable.')) results IN ('bla') if your macro variable contains bla - your code would not work - i.e. not create the correct result.
Respected Advisor
Posts: 3,867

Re: dynamic column name in proc sql with macro variable

Posted in reply to csetzkorn

csetzkorn wrote:

I tend to use a lot of dynamic sql in poc sql. Something like this works fine:

 

WHERE TableAlias.ColumnName IN (%bquote('&MacroVariable.'))

However for some reason, this:

 

SELECT &MacroVariableContainingColumnName. FROM ...

 

does not work. Any ideas?


Does not work is rather vague.  Is there an error message you would like to share?

 

 

Super Contributor
Posts: 359

Re: dynamic column name in proc sql with macro variable

Posted in reply to csetzkorn

Hello,

 

%let myvar=age;

proc sql;
SELECT &myvar.
FROM sashelp.class;
quit;

Please show a complete example where the problem occur.

Super User
Posts: 6,939

Re: dynamic column name in proc sql with macro variable

Posted in reply to csetzkorn

 Are you sure you didn't get it backwards?  The SELECT statement should be fine (except that you refer to a 33-character name for a macro variable).  But the IN operator is likely wrong.  For a single variable value, you could certainly use:

 

WHERE TableAlias.ColumnName = "&Macrovariable."

 

And if &MACROVARIABLE actually contains more than one value such as NY MA CT, the syntax would be wrong:

 

WHERE TableAlias.ColumnName IN ('NY MA CT')

Regular Contributor
Posts: 210

Re: dynamic column name in proc sql with macro variable

Posted in reply to Astounding
The IN approach definitely work (-: It produces: WHERE TableAlias.ColumnName IN ('NY MA CT')
Super User
Posts: 6,939

Re: dynamic column name in proc sql with macro variable

Posted in reply to csetzkorn

Yes, IN gives exactly the result you listed.  But that's the wrong result.  That does NOT give you observations for "NY" or "MA" or "CT".  It gives you observations where your variable is 8 characters long, equal to the string "NY MA CT".  That result has nothing to do with macro language ... it's the meaning of IN.  If you want to compare to three separate possible values, you have to list each of them separately in its own set of quotes:  IN ('NY' 'MA' 'CT').

Regular Contributor
Posts: 210

Re: dynamic column name in proc sql with macro variable

Posted in reply to Astounding

The IN clause is just an example - that's not what the question is about ...

Solution
‎10-14-2017 03:26 AM
Super User
Super User
Posts: 8,289

Re: dynamic column name in proc sql with macro variable

[ Edited ]
Posted in reply to csetzkorn

This message:

Table does not contain MacroVariableContainingColumnName

Makes it sound like your have not defined your macro variable.  When the SAS macro processor sees what looks like a macro variable reference such as &XX and the macro variable does not exist then it just ignores it and passes the &XX on to SAS do deal with.  So in this case it looks like that happened and the result was that your table does not have a variable with that name.  Make sure you have defined your macro variable before using it.

 

Note that SAS names cannot be longer than 32 characters and that name is 33 characters. So that cannot be used for either a macro variable name,  a variable name or a dataset member name.

 

Perhaps you are trying to use the & as a trigger for some type of "dynamic" SQL in passthru code?  In that case you should try masking the & so that the SAS macro processor doesn't try to interpret it.

 

proc sql ;
connect to .... as MYDB ;
execute (
.... %nrstr(&something_or_other) ....
) by MYDB;
quit;
Regular Contributor
Posts: 210

Re: dynamic column name in proc sql with macro variable

Thanks. I am such a fool I passed my macro variable into the macro that contains the described code wrongly (i.e. MV instead of &MV.). Your post helped me solve my problem. Thanks!
☑ This topic is solved.

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

Discussion stats
  • 14 replies
  • 2064 views
  • 3 likes
  • 7 in conversation