How to drop tables with same prefix witout a cicle?

[AnaC]

I was expecting someting like this:

proc sql;

drop table WORK.TABLETODROP_:;

quit;

Tables to be droped:

TABLETODROP_1

TABLETODROP_2

TABLETODROP_3

...

TABLETODROP_20

Thanks,

Ana

[novinosrin]

HI @AnaC   Wow, I really like your thinking in wanting Proc SQL to be compatible with variable or dataset names lists. I'm afraid lists(shortened versions) do not work in Proc SQL. You would have to separate one by one with a comma in between. Nice try though! 🙂

Alternatively, you can write a small macro to hold that list separated by ',' and call that in the drop table clause in Proc SQL

[FreelanceReinh · Accepted solution]

Hi @AnaC,

PROC DELETE can do this (not with a colon list, though):

proc delete data=TABLETODROP_1-TABLETODROP_20;
run;

[AnaC]

Thanks, it works!! Now I don't need to type all the table names.
For know I know how many table a have but some times I might have 30 or 40. My initial ideia with the ":" would be to cover this situation.

[FreelanceReinh]

If the deletion is just a manual step (as opposed to a production program whose log must be clean), you can use a "large enough" end value (like 99) for the list of numeric suffixes. For each non-existing dataset in the list you will get a

WARNING: File WORK.TABLETODROP_##.DATA does not exist.

in the log, though.

For a clean log @Tom's suggested code using PROC DATASETS is a better solution in case of an unknown number of files to be deleted having the same prefix. With this procedure the prefix-colon abbreviation works as it does in other contexts (e.g. SET statement). And, no, it has nothing to do with dataset names containing a ":" -- colons in dataset names are always invalid, even with the option setting VALIDMEMNAME=EXTEND (see Summary of Extended Rules for Naming SAS Data Sets and SAS Variables).

[AnaC]

So with my data names I should writ something like:
proc datasets nolist lib=work;
delete tabletodrop: ;
run;quit;

[Tom]

Trying to get SQL to act like SAS code is probably not a useful path to follow.

I would think that PROC DELETE should allow this.

proc delete data=have: ;
run;

But it doesn't.

However you can use to more cumbersome PROC DATASETS instead.

proc datasets nolist lib=work;
  delete have: ;
run;quit;

[AnaC]

A I haven't try this because it seams that is for when I have ":" in the table name and not when a have a lot of table with the same prefix. Am I understanding wrong this code?