Use macro variables in Proc SQL in SAS

Question

Two macro variables are defined for table schema and table name and they are used in the proc sql statement in SAS (postgresql interface to Amazon RedShift), sql statement can't be read correctly.

%let table_schema = 'sales';
%let table_name = 'summary_table';

proc sql;
connect to ODBC(DSN='redshift_db', user=masteruser password='*pwd');
create table column_names as
select * from connection to odbc(
select distinct(column_name) as col_name from information_schema.columns where table_schema = &table_schema and table_name = &table_name;
);

create table dt as
select * from connection to odbc(
select * from &table_schema..&table_name;
);

QUIT;

The first table creation throws an error:

ERROR: CLI describe error: ERROR: column "summary_table" does not exist in columns;

The "summary_table" actually exists.

The second table creation throws an error:

ERROR: CLI describe error: ERROR: syntax error at or near "'sales'"; No query has been executed with that handle

which is invalid either.

Answer 1

Check your syntax.

Look at the ERROR: column "summary_table" does not exist in columns;

What that is saying is that the COLUMN named "summary_table" does not exist on the table named "columns". The where clause is trying to compare 2 columns, not a column and a string.

Write the query without macros and get that to work. Then attempt to put the macros in.

This is a syntax issue with the PostGreSQL ODBC driver, not SAS.

Answer 2

I haven't used the PostGreSQL driver before but I know different DBs handle quotes differently. It may be that PostGreSQL wants double quotes instead of single. If this is the case then I would change your code like so:

%let table_schema = sales;
%let table_name = summary_table;

proc sql;
connect to ODBC(DSN='redshift_db', user=masteruser password='*pwd');
create table column_names as
select * from connection to odbc(
select distinct(column_name) as col_name from information_schema.columns where table_schema = "&table_schema" and table_name = "&table_name";
);

create table dt as
select * from connection to odbc(
select * from &table_schema..&table_name;
);

QUIT;

Note that I've removed the single quotes from the %let statments and added double quotes to the where clause in the first query.

Removing the singles quotes from the macro vars will also fix the syntax error you are experiencing with the second query as you were effectively trying to do the following:

select * from 'sales'.'summary_table';

... which is most likely not correct. Again I haven't used PostGreSQL so I can't say for certain - please correct me if I'm wrong and that is correct syntax for it.

Finally, I'd recommend that when working with macro variables in general, that you don't put single quotes around your strings. ie.

%let table_schema = 'sales'; * AVOID THIS WHEN POSSIBLE;

vs

%let table_schema = sales;   * USE THIS APPROACH WHEN POSSIBLE;

Put double quotes around the places where macro vars are used instead and you'll probably find things a little easier. Of course there's always circumstances where you may want quotes in the value of the macro variable, but as a general rule of thumb I avoid doing this whenever possible.