Using macro in proc sql with ODBC connection to Access database

0
votes

I have a macro variable called fileName. I tried to use it in the proc sql which connects to an Access databse through ODBC. However, the code I have either has an error or its not recognizing the macro variable.

Here is my code:

%let fileName=MYFILE.NAME
proc sql;
connect to odbc ("DSN=MS Access Database;"||
               "DBQ=&dbname;"||
               "FIL=MS Access;" ||
               "MaxBufferSize=512;" ||
               "PageTimeout=600;" ||
               "UID=admin");
create table t1 as
select * from connection to odbc
(SELECT * FROM tableA
where FileName='&fileName');
quit;

This returns 0 row. If I replace the macro variable with the real value in the query, it will return 1 row with the correct data.

If I use double quotation around the &fileName, I get the following error: ERROR: CLI describe error: [Microsoft][ODBC Microsoft Access Driver] '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Could anyone tell me how should I pass the macro variable to the query? Thanks.

3
sasproc-sql
Your missing a semicolon after the macro variable declaration, before the start of the proc sql. - Reeza
I had it in my original copy. Forgot to put it when I copied the code here. - Yolanda
If you have working code, post an example of that with the log. You definitely need double quotes for the macro variable to resolve. Any consideration of using the libname method to access the ACCESS DB instead? - Reeza
Thanks Reese. I tried using libnmae. It works. However, I believe when libname is used, SAS reads in the whole table before it runs the query. I tried to avoid that. - Yolanda
That used to be true. SAS now uses implicit pass-through so the query performances should be very similar. Access tables should be small enough to not have issues, if it was a server would be a different issue. One reason to use explicit pass through is when table names/column names are longer than 32 characters. - Reeza

3 Answers

1
votes

External DBMS do not handle single and double quotes the way SAS does : double quotes enclose variable name, while single quotes enclose strings - you cannot use double quote to resolve macros and use this result as a string in i;e. a where clause. You should code

%let schema=SCOTT; where owner = %unquote(%str(%'&schema%'))

see http://support.sas.com/techsup/notes/v8/00/539.html

0
votes

You need to make sure that if you want to resolve your macro variable, it isn't wrapped within single quotes. This line:

where FileName='&fileName');

...needs to be:

where FileName="&fileName");
0
votes

Try striping leading and trailing space incase they were created by the input buffer.

where FileName = "%sysfunc(strip(&fileName))"  );