topic Re: optional drop table in New SAS User

optional drop table

ppinedo — Wed, 17 Apr 2024 17:24:33 GMT

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?

Re: optional drop table

ballardw — Wed, 17 Apr 2024 17:47:20 GMT

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.

Re: optional drop table

ppinedo — Wed, 17 Apr 2024 18:11:16 GMT

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

Re: optional drop table

ballardw — Wed, 17 Apr 2024 19:05:14 GMT

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

Re: optional drop table

yabwon — Wed, 17 Apr 2024 19:10:11 GMT

Try this:

data x;
  x=42;
run;

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

Bart

Re: optional drop table

Tom — Wed, 17 Apr 2024 19:22:04 GMT

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.

Re: optional drop table

Patrick — Thu, 18 Apr 2024 02:29:53 GMT

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;

Re: optional drop table

ppinedo — Tue, 23 Apr 2024 12:56:27 GMT

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

Re: optional drop table

Ksharp — Wed, 24 Apr 2024 05:35:03 GMT

An alternative way like Patrick is using PROC DELETE.

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