optional drop table

[ppinedo]

Hi friends,

I don't now how to drop a table which maybe don´t exist:

If I use this code:

proc sql;
if exist(select * from x) then
drop table x;
quit;
run;

I get this error message: "Statement is not valid or it is used out of proper order."

Another code:

proc sql;
drop table if exists x;
quit;
run;

SAS says: "ERROR: Syntax error, expecting one of the following: ;, (, ',', '.', INDEX, TABLE, VIEW. "🤔

¿Anybody knows how to  do it?

[ballardw]

When you get an error you should copy from the log the code for the procedure or step and all the messages related. Then on the forum open a text box on the forum by clicking on the </> icon that appears above the message window.

That would show something like:

110  proc sql;
111  if exist(select * from x) then
     --
     180
ERROR 180-322: Statement is not valid or it is used out of proper order.

112  drop table x;
113  quit;

Which the -- is under the word IF, indicating that is the problem. SQL does not use IF/Then. There is a CASE When for selecting variables.

I don't think that the proc sql Drop to remove tables will use a variable read from a table.

[ppinedo]

That code is from another post in this foro, it use "IF" in his/her code @ballardw

[ballardw]

---

@ppinedo wrote:

That code is from another post in this foro, it use "IF" in his/her code @ballardw

---

Proc SQL can be used with a connection to external databases to pass through code that is specific to that data base that often has extensions that will not run with "pure" SAS SQL. So with out seeing the specific post to tell if that was the case I'll stand by the the error is related to the IF.

[yabwon]

Try this:

data x;
  x=42;
run;

proc sql;
  %sysfunc(ifc(%sysfunc(exist(x)),
    drop table x,
  ));;
quit;

Bart

[Tom · Accepted solution]

Please clarify what you are trying to do.

Your words make it sound like you want to drop the dataset when it does exist (you cannot drop a dataset that does not exist).  Perhaps you are worried about a warning message if you try to drop a dataset that does not exist?

1    proc delete data=no_such_data ; run;

WARNING: File WORK.NO_SUCH_DATA.DATA does not exist.
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


2    proc sql;
3     drop table no_such_data;
WARNING: File WORK.NO_SUCH_DATA.DATA does not exist.
WARNING: Table WORK.NO_SUCH_DATA has not been dropped.
4    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

If so then you probably need to conditionally generate CODE.

The SAS macro language is good at that.

5    proc sql;
6    %if %sysfunc(exist(no_such_data)) %then %do;
7     drop table no_such_data;
8    %end;
9    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

If instead you meant to drop the table when it has no observations then you will need a different test.

[Patrick]

The IF EXIST clause doesn't exist in the SAS SQL flavor which is why you're getting an error. 

The following syntax works for deleting SAS tables that may or may not exist.

proc datasets lib=work nolist nowarn;
  delete mytable;
quit;

[ppinedo]

Sorry for my delay @ballardw, you're right 👍 ( IF don't exist in SQL, I used it in MySQL)

@Tom& @Patrick yours codes are perfect for me 👍, I don't want receive WARNINGs if table doesn't exist

[Ksharp]

An alternative way like Patrick is using PROC DELETE.

options nodsnferr;
proc delete data=work.mytable;
run;