BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csetzkorn
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

14 REPLIES 14
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

PaigeMiller
Diamond | Level 26

@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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

csetzkorn
Lapis Lazuli | Level 10
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 ...
PaigeMiller
Diamond | Level 26

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
csetzkorn
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19

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

 

 

gamotte
Rhodochrosite | Level 12

Hello,

 

%let myvar=age;

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

Please show a complete example where the problem occur.

Astounding
PROC Star

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

csetzkorn
Lapis Lazuli | Level 10
The IN approach definitely work (-: It produces: WHERE TableAlias.ColumnName IN ('NY MA CT')
Astounding
PROC Star

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

csetzkorn
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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;
csetzkorn
Lapis Lazuli | Level 10
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!

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
  • 14 replies
  • 11798 views
  • 3 likes
  • 7 in conversation