topic Re: Check if table exists in database in SAS Programming

Check if table exists in database

AJ_Brien — Mon, 13 Jan 2020 20:06:57 GMT

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.

Re: Check if table exists in database

Reeza — Mon, 13 Jan 2020 20:16:13 GMT

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.

Re: Check if table exists in database

Tom — Mon, 13 Jan 2020 20:18:53 GMT

What I had to do to check if a Teradata table or view existed was query the Teradata metadata directly instead.

Here is part of a macro I made:

*----------------------------------------------------------------------------;
* Check if table or view exists ;
*----------------------------------------------------------------------------;
select obj into :result trimmed from connection to &connection
  (select case when (tablekind in ('T','O')) then 'TABLE'
          else 'VIEW' end as obj
    from dbc.tablesv
    where databasename = %squote(&db) and tablename= %squote(&table)
      and tablekind in ('V','T','O')
  )
;
%let exists=&sqlobs;

I also included code to check if there is any Volatile table with that name.

%if (^&exists and &db=&userid) %then %do;
*----------------------------------------------------------------------------;
* Check for any Volatile tables ;
*----------------------------------------------------------------------------;
  select 1 into :exists from connection to &connection (help volatile table) ;

  %if (&exists) %then %do;
*----------------------------------------------------------------------------;
* Check if this Volatile table exists ;
*----------------------------------------------------------------------------;
    select 'VOLATILE TABLE' into :result
      from connection to &connection (help volatile table)
%*----------------------------------------------------------------------------
Set VARNAME based on VALIDVARNAME setting.
-----------------------------------------------------------------------------;
%if %sysfunc(getoption(validvarname))=ANY %then
      where upcase('table name'n) = "&table"
;%else
      where upcase(table_name) = "&table"
;
    ;
    %let exists=&sqlobs;
  %end;
%end;

Re: Check if table exists in database

AJ_Brien — Mon, 13 Jan 2020 20:20:44 GMT

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

Re: Check if table exists in database

Reeza — Mon, 13 Jan 2020 20:24:29 GMT

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.

Re: Check if table exists in database

AJ_Brien — Mon, 13 Jan 2020 20:31:11 GMT

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

Re: Check if table exists in database

Tom — Mon, 13 Jan 2020 20:46:07 GMT

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.))

Re: Check if table exists in database

AJ_Brien — Mon, 13 Jan 2020 20:50:58 GMT

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.

Re: Check if table exists in database

Tom — Mon, 13 Jan 2020 21:07:20 GMT

That error is from some other mistake.

Re: Check if table exists in database

Tom — Mon, 13 Jan 2020 21:17:06 GMT

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

Re: Check if table exists in database

AJ_Brien — Mon, 13 Jan 2020 21:19:06 GMT

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.

Re: Check if table exists in database

Tom — Mon, 13 Jan 2020 21:45:05 GMT

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.

Re: Check if table exists in database

Patrick — Mon, 13 Jan 2020 22:05:00 GMT

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;

Re: Check if table exists in database

Reeza — Mon, 13 Jan 2020 22:48:26 GMT

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

Re: Check if table exists in database

Patrick — Tue, 14 Jan 2020 00:57:31 GMT

@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;

Re: Check if table exists in database

Tom — Tue, 14 Jan 2020 01:59:43 GMT

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.

Re: Check if table exists in database

Patrick — Tue, 14 Jan 2020 02:38:38 GMT

@Tom wrote:

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.

It might not - but @Reeza statement was for "DB tables" in general and for this the Oracle engine appeared good enough to me.

Also: If the information doesn't make it into the dictionary tables then how comes we can normally see such tables in UI's like SAS EG under the library node. So I'm rather guessing that if DB tables aren't available in the SAS dictionary then there are some DB permissions "missing" site specific which prohibit the SAS user to query the DB metadata.

Re: Check if table exists in database

Reeza — Tue, 14 Jan 2020 16:24:47 GMT

It may depend on if your system registered the tables, but historically any time I would connect to DB2, MS SQL DB the table names would not show in the dictionary.table.

It may vary based on the connection type (ODBC vs DB Specific drivers)??

It definitely does not work for all DB library so not sure what the rules are there.

Re: Check if table exists in database

avellani — Thu, 09 Dec 2021 18:45:13 GMT

This worked great for me.
And instead of this:

%else %do;
%put Could not find &dsname ;
%end;

I created an empty global temp table:
%else %do;
/* If Oracle table does not exists, create it */
proc sql ;
connect to oracle (path="hcemi" user=&hcemi_u. password="%superq(hcemi_p)"
connection=shared adjust_byte_semantic_column_lengths=yes);
execute (create global temporary table "%sysuserid..temptrans (mbr_id char(46))) by oracle ;
quit ;
%end ;

Thanks!