Sas drop teradata table issues

[Amicheals]

Greetings,

I have a table in Teradata named DataLoad. It has two columns, LoadID and EmpAI

I am trying to create a simple script that will check the table DataLoad and see if it has any rows inside it, If it does it does nothing, if there is no data in the table it drops it. Any suggestions on how to achieve this?

[jklaverstijn]

Hi @Amicheals,

You could check if there are any rows by trying to read the first one. If there is no first row then drop the table:

There are other ways/. You say you do not want to do this using PROC SQL. That would normally need some explanation because that eliminates some techniques like explicit passthrough. But I am not inviting you to have that discussion.

%macro deletewhenempty;

%let hasrows=0;
data _null_;
   set tdlib.DataLoad;
   call symput('hasrows', _n_);
   stop;
run;

%if &hasrows=0 %then %do;
   proc datasets lib=tdlib nolist nowarn;
      delete DataLoad;
   quit;
%end;

%mend;

%deletewhenempty;

I hope this helps,

- Jan.

[Amicheals]

-Jan I spoke to soon, I can use a proc sql statement.

[jklaverstijn]

Then the code to drop the table could be as simple as:

proc sql;
   connect to teradata as tera ( user=testuser password=testpass database=mydb );
   execute (drop table DataLoad) by tera;
   execute (commit) by tera;
   disconnect from tera;
quit;

The need for a commit depends on the mode of your connection, ANSI or Teradata.

Taking it further you could wrap all the login in a Teradata stored procedure. This is however not my forte. And I think in the use case at hand is too simple to go that path.

Regards,

- Jan.

[Amicheals]

Jan,

is there a way to add something into that proc sql so if the table does not exist it just moves on to the next step?

[jklaverstijn]

I would handle that in the macro:

%macro deletewhenempty;

%if sysfunc(exist(tdlib.DataLoad)) %then %do;
   %let hasrows=0;
   data _null_;
      set tdlib.DataLoad;
      call symput('hasrows', _n_);
      stop;
   run;

   %if &hasrows=0 %then %do;
      proc datasets lib=tdlib nolist nowarn;
         delete DataLoad;
      quit;
   %end;
%end;
%mend;

%deletewhenempty;

The code not yet enterprise ready. Eg. I would eliminate the repetition of lib and table name, but I leave that to you as home woirk. 🙂

Hope this helps,

-- Jan.

[Amicheals]

Jan One last question. In that code you sent me above. What If I just wanted to drop the table regardless if there is data in it.

So check for DataLoad, if exists drop otherwise move to next step. Sorry to throw a wrench into it

[jklaverstijn]

That would mean a simplification of the code. Take out the '%if hasrows' condition:

%macro deletewhenexists;

%if sysfunc(exist(tdlib.DataLoad)) %then %do;

   proc datasets lib=tdlib nolist nowarn;
      delete DataLoad;
   quit;

%end;
%mend;

%deletewhenexists;

You could also, amongst others, use PROC DELETE. see Chris Hemedinger's discussion on this for the pro's and con's of this procedure.

Hope this helps,

-- Jan.

[Amicheals]

Jan,

Thank you. I am getting the following error when trying to run that code

Required operator not found in expression: sysfunc(exist(tdlib.DataLoad))

any ideas?

[jklaverstijn]

Ah yes, I never actually tested the code 😞

It's

%if %sysfunc(exist(tdlib.DataLoad)) %then %do;

sysfunc is a macro function so it needs to be prefixed with a percent sign.

Please keep in mind that I present suggestions and not complete, robust and fully tested code. But I do hope I have now put you on the right path. Experience and further education will help you to troubleshoot and extrapolate on these suggestions.

Hope this helps,

-- Jan.