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.
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.))
That error is from some other mistake.
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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.