topic Re: Error trapping for presence of table within an Access DB accessed through ODBC in Microsoft Integration with SAS

Error trapping for presence of table within an Access DB accessed through ODBC

pharmKitty — Sat, 21 Jul 2012 17:49:20 GMT

Does anyone know how I can check for the presence of particular table within an Access DB from within my SAS code? If you know of any other error checking I should do ivolving working with an Accdess Database in SAS, I would appreciate knowing that too.

THanks in advance for your time and help.

Re: Error trapping for presence of table within an Access DB accessed through ODBC

PGStats — Sat, 21 Jul 2012 18:22:20 GMT

Assuming that your ODBC library has libname="T", you can check for the presence of table "Test" like this :

proc sql noprint;
select sum(upcase(memname)="TEST") into :testPresent
from dictionary.tables
where libname="T"; /* use uppercase */

%put Present: &testPresent;

dictionary.tables is dynamic, i.e. it reflects the current state of the database in real time.

Re: Error trapping for presence of table within an Access DB accessed through ODBC

Patrick — Sun, 22 Jul 2012 10:05:30 GMT

Below code is what SAS DI Studio generates for such a test:

%let etls_tableExist = %eval(%sysfunc(exist(<libref>.<table>, DATA)) or %sysfunc(exist(<libref>.<table>, VIEW)));

&etls_tableExist is '0' if the table/view does not exist and '1' if the table/view exists.

The pre-condition for this code to work is of course that you've issue a libname statement using the ODBC engine.

If you're just using SQL pass-through and there is no libname defined at all then you would also have to write some SQL pass-through code querying the dictionary tables on the DB side to determine if a table exists or not.

Re: Error trapping for presence of table within an Access DB accessed through ODBC

pharmKitty — Sun, 22 Jul 2012 15:13:56 GMT

PG, Thank you very much for your fast reply. I will try your solution Monday when I get to work. Then I will mark correct/helpful. My home SAS has expired 😞

Re: Error trapping for presence of table within an Access DB accessed through ODBC

pharmKitty — Sun, 22 Jul 2012 15:17:43 GMT

Patrick, thanks very much for your reply. I do want to test whether the table exists to determine what will happen next so I really like this answer a lot. I can't wait to get to work tomorrow to try this out. Thanks also for such a detailed explanation! I will mark as helpful/correct tomorrow after I try both these solutions out.