topic Sas drop teradata table issues in New SAS User

Sas drop teradata table issues

Amicheals — Thu, 27 Dec 2018 15:19:17 GMT

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?

Re: Sas drop teradata table issues

jklaverstijn — Thu, 27 Dec 2018 15:14:25 GMT

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.

Re: Sas drop teradata table issues

Amicheals — Thu, 27 Dec 2018 15:19:53 GMT

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

Re: Sas drop teradata table issues

jklaverstijn — Thu, 27 Dec 2018 15:32:08 GMT

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.

Re: Sas drop teradata table issues

Amicheals — Thu, 27 Dec 2018 16:25:53 GMT

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?

Re: Sas drop teradata table issues

jklaverstijn — Thu, 27 Dec 2018 16:45:59 GMT

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.

Re: Sas drop teradata table issues

Amicheals — Thu, 27 Dec 2018 17:03:39 GMT

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

Re: Sas drop teradata table issues

jklaverstijn — Fri, 28 Dec 2018 12:00:49 GMT

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.

Re: Sas drop teradata table issues

Amicheals — Fri, 28 Dec 2018 14:34:23 GMT

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?

Re: Sas drop teradata table issues

jklaverstijn — Fri, 28 Dec 2018 14:44:11 GMT

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.