topic Re: How to drop tables with same prefix witout a cicle? in SAS Programming

How to drop tables with same prefix witout a cicle?

AnaC — Fri, 10 Jan 2020 13:28:56 GMT

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

Re: How to drop tables with same prefix witout a cicle?

novinosrin — Fri, 10 Jan 2020 13:35:29 GMT

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

Re: How to drop tables with same prefix witout a cicle?

FreelanceReinh — Fri, 10 Jan 2020 13:58:51 GMT

Hi @AnaC,

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

proc delete data=TABLETODROP_1-TABLETODROP_20;
run;

Re: How to drop tables with same prefix witout a cicle?

Tom — Fri, 10 Jan 2020 13:59:44 GMT

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;

Re: How to drop tables with same prefix witout a cicle?

AnaC — Mon, 13 Jan 2020 08:44:44 GMT

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?

Re: How to drop tables with same prefix witout a cicle?

AnaC — Mon, 13 Jan 2020 08:47:54 GMT

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.

Re: How to drop tables with same prefix witout a cicle?

FreelanceReinh — Mon, 13 Jan 2020 09:34:45 GMT

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

Re: How to drop tables with same prefix witout a cicle?

AnaC — Mon, 13 Jan 2020 09:58:57 GMT

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