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
Hi @AnaC,
PROC DELETE can do this (not with a colon list, though):
proc delete data=TABLETODROP_1-TABLETODROP_20;
run;
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
Hi @AnaC,
PROC DELETE can do this (not with a colon list, though):
proc delete data=TABLETODROP_1-TABLETODROP_20;
run;
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).
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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.