BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pharmKitty
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

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
pharmKitty
Calcite | Level 5

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 😞

Patrick
Opal | Level 21

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.

pharmKitty
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 4 replies
  • 1265 views
  • 3 likes
  • 3 in conversation