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.
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.
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.
PG
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 😞
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!