topic Re: dynamic column name in proc sql with macro variable in SAS Programming

dynamic column name in proc sql with macro variable

csetzkorn — Fri, 13 Oct 2017 14:58:52 GMT

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?

Re: dynamic column name in proc sql with macro variable

RW9 — Fri, 13 Oct 2017 12:38:51 GMT

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.

Re: dynamic column name in proc sql with macro variable

data_null__ — Fri, 13 Oct 2017 12:39:03 GMT

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

Re: dynamic column name in proc sql with macro variable

gamotte — Fri, 13 Oct 2017 12:40:16 GMT

Hello,

%let myvar=age;

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

Please show a complete example where the problem occur.

Re: dynamic column name in proc sql with macro variable

PaigeMiller — Fri, 13 Oct 2017 12:55:58 GMT

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

Re: dynamic column name in proc sql with macro variable

Astounding — Fri, 13 Oct 2017 12:57:14 GMT

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

Re: dynamic column name in proc sql with macro variable

RW9 — Fri, 13 Oct 2017 14:03:42 GMT

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.

Re: dynamic column name in proc sql with macro variable

csetzkorn — Fri, 13 Oct 2017 14:43:59 GMT

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.

Re: dynamic column name in proc sql with macro variable

csetzkorn — Fri, 13 Oct 2017 14:46:59 GMT

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

Re: dynamic column name in proc sql with macro variable

PaigeMiller — Fri, 13 Oct 2017 14:47:47 GMT

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.

Re: dynamic column name in proc sql with macro variable

csetzkorn — Fri, 13 Oct 2017 14:49:22 GMT

The IN approach definitely work (-: It produces: WHERE TableAlias.ColumnName IN ('NY MA CT')

Re: dynamic column name in proc sql with macro variable

Astounding — Fri, 13 Oct 2017 16:30:43 GMT

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

Re: dynamic column name in proc sql with macro variable

csetzkorn — Fri, 13 Oct 2017 17:08:34 GMT

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

Re: dynamic column name in proc sql with macro variable

Tom — Fri, 13 Oct 2017 17:36:25 GMT

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;

Re: dynamic column name in proc sql with macro variable

csetzkorn — Sat, 14 Oct 2017 07:26:19 GMT

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!