Check if table exists in database

[AJ_Brien]

Hello,

I'm working on a code that will help determine if a table exists in a database or not. If it does, then the table is deleted, if it doesn't exist then it creates that table:

libname db1 teradata user="xxx" password="xxx" tdpid=rchtera mode=ansi database=xx; 


%macro check_table(db_name,table_name);
  %if %sysfunc(exist(&db_name.&table_name.)) %then %do;
    %put The table exists.;
proc sql;
 drop table (&db_name.&table_name.) ;
quit;
  %end;
  %else %do;
      %Put Table does not exist;
  %end;
%mend;

%check_table(db1,T1_data);

proc sql;
connect to teradata (user="xx" password="xx" tdpid=RCHTERA mode=teradata); 
create table db1.T1_data (FASTLOAD=YES) as 
select * from work.T1_data;
disconnect from teradata;
quit;

When I test this, even though the table db1.T1_data exists, it still keeps displaying 'Table does not exist' as output. Why would the exist condition not work?

I also checked to see that the correct values of &db_name.&table_name. are being passed into the macro.

Appreciate the help in helping me understand what I'm missing!

Thank you.

[Reeza]

I don't think the exist function will work on an external DB. You can try and query the server metadata via the dictionary.table table depending on the server.

[AJ_Brien]

Thank you!
I tried the same macro with the work table, but even for that it displayed that the table does not exist, even though it did. If it doesn't work on external DB, it should still work when I pass a table by work.abc

[Reeza]

Yes, it should work for an internal data set. Post the log from running it with an internal data set and the MPRINT/SYMBOLGEN options on.

[AJ_Brien]

Please find the log:

25 GOPTIONS ACCESSIBLE;
26 options mautosource nocenter source2 mlogic symbolgen mprint;
27
28 %macro check_table(db_name,table_name);
29 %if %sysfunc(exist(&db_name.&table_name.)) %then %do;
30 %put &=db_name ;
31 %put &=table_name ;
32 %put The table exists;
33 proc sql;
34 drop table (&db_name.&table_name.) ;
35 quit;
36 %end;
37 %else %do;
38 %put &=db_name ;
39 %put &=table_name ;
40 %Put Table does not exist;
41 %end;
42 %mend;
43
44 %check_table (work,abc);
MLOGIC(CHECK_TABLE): Beginning execution.
MLOGIC(CHECK_TABLE): Parameter DB_NAME has value work
MLOGIC(CHECK_TABLE): Parameter TABLE_NAME has value abc
SYMBOLGEN: Macro variable DB_NAME resolves to work
SYMBOLGEN: Macro variable TABLE_NAME resolves to abc
MLOGIC(CHECK_TABLE): %IF condition %sysfunc(exist(&db_name.&table_name.)) is FALSE
MLOGIC(CHECK_TABLE): %PUT &=db_name
SYMBOLGEN: Macro variable DB_NAME resolves to work
DB_NAME=work
2 The SAS System 13:16 Monday, January 13, 2020

MLOGIC(CHECK_TABLE): %PUT &=table_name
SYMBOLGEN: Macro variable TABLE_NAME resolves to abc
TABLE_NAME=abc
MLOGIC(CHECK_TABLE): %PUT Table does not exist
Table does not exist
MLOGIC(CHECK_TABLE): Ending execution.
45
46

[Tom]

Your code is missing a period.  You are checking for the existence of the member named WORKABC in the default library (normally WORK) instead of checking for the member ABC in the WORK library.

The macro processor will use the first period as an indication of where macro variable name ends.  So to include a period in the generated code you need to add another one.

 %sysfunc(exist(&db_name..&table_name.))

[AJ_Brien]

I tried that and it gave me this error:
MPRINT(CHECK_TABLE): proc sql;
SYMBOLGEN: Macro variable DB_NAME resolves to work
SYMBOLGEN: Macro variable TABLE_NAME resolves to abc
NOTE: Line generated by the invoked macro "CHECK_TABLE".
44 (&db_name..&table_name.) ; quit;
_
22
200
MPRINT(CHECK_TABLE): drop table () ;
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

ERROR 200-322: The symbol is not recognized and will be ignored.

So I then added quotes like this
("&db_name."."&table_name.")
and it went back to displaying table does not exist.
I also tried this ("&db_name.".&table_name.), that didn't make a difference either.

[Tom]

That error is from some other mistake.

[Tom]

Remember when pasting in text (or editing it) to use the Insert Code or Insert SAS code buttons.

Try this fixed version:

%macro check_table(db_name,table_name);
%local dsname ;
%let dsname=&table_name ;
%if %length(&db_name) %then %let dsname=&db_name..&dsname;

%if %sysfunc(exist(&dsname)) %then %do;
  %put Found table &dsname ;
  proc delete data=&dsname; run;
%end;
%else %do;
  %put Could not find &dsname ;
%end;
%mend;

Example:

827   options mprint;
828   %check_table(,abc);
Could not find abc
829   data abc;
830   run;

NOTE: The data set WORK.ABC has 1 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


831   %check_table(,abc);
Found table abc
MPRINT(CHECK_TABLE):   proc delete data=abc;
MPRINT(CHECK_TABLE):   run;

NOTE: Deleting WORK.ABC (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

[AJ_Brien]

This works. Could you help me understand how moving those 2 macro variable names into a single dsname made a difference? Or how could I have modified my code to correct that? Would help me avoid similar mistakes in the future 🙂 Thank you.

[Tom]

As I said before all you needed to do was add the extra period, where ever you needed it. Making the full dataset name from the two pieces into one macro variable just makes so you only have a single place to check to make sure the period is getting inserted properly.

[Patrick]

Querying the database metadata directly as @Tom proposes is certainly an option. However this requires database specific syntax.

Below two alternative options.

Option 2 is a bit "brute force" and I'm also not 100% sure that it will work for Teradata (it doesn't for Hadoop).

/* option 1: query SAS dictionary tables */
%macro check_table(db_name,table_name);
  %local delflg;
  %let delflg=0;
  proc sql;
    select count(*) into :delflg
    from dictionary.tables
    where libname="%upcase(&db_name)" and memname="%upcase(&table_name)"
    ;
  quit;
  %if &delflg=1 %then %do;
    %put The table exists.;
      proc sql;
       drop table (&db_name..&table_name.) ;
      quit;
  %end;
  %else %do;
      %Put Table does not exist;
  %end;
%mend;
%check_table(db1,T1_data);

/* option 2: not 100% if this works with Teradata */
%macro dropTbl_ifExist(db_name,table_name);
  proc datasets lib=&db_name nolist nowarn;
    delete &table_name;
    run;
  quit;
%mend;

[Reeza]

Patrick, unfortunately DB tables do not end up in the dictionary.tables data set 😞

[Patrick]

---

@Reeza wrote:
Patrick, unfortunately DB tables do not end up in the dictionary.tables data set 😞

---

Hi @Reeza 

How do you get to such a conclusion? Below works for me.

libname oratest oracle user=<user> pw=<password>;
proc sql;
  select *
  from dictionary.tables
  where libname='ORATEST'
  ;
quit;

[Tom]

Not sure how much the results from using SAS/Access to Oracle to access an Oracle database translate into using SAS/Access to ODBC to connect to a Teradata database.