topic Re: Finding a series if the "number" is in the middle of a variable in SAS Programming

Finding a series if the "number" is in the middle of a variable

sam_sas2 — Wed, 26 Aug 2020 12:52:20 GMT

Hi All,

So I have this task of deleting around 100 datasets. But the problem is the datasets are named this way: adc_1_a, adc_2_a,......,adc_45_d, etc.

So I tried doing this:

proc datasets nolist lib=hmp; delete adc_1_: - adc_45_; run;quit;

but this throws up an error.
How do I achieve this? Thanks in advance 🙂

Re: Finding a series if the "number" is in the middle of a variable

RichardDeVen — Wed, 26 Aug 2020 16:43:14 GMT

You can use metadata table DICTIONARY.TABLES to construct a list of tables that match some naming convention detectable with a regular expression pattern.

Example:

Some sample data sets with the same common prefix (adc_:) but not all are adc_#_<letter>

* create some sample data sets whose names start with adc_;
data adc_1_a adc_2_a adc_45_d adc_patients;
x=1;
run;

%let tables=;

proc sql;
  reset noprint;
  select memname into :tables separated by ' '
  from dictionary.tables
  where libname = 'WORK'                          /* sample data sets are in the WORK library */
    and prxmatch ('/adc_\d+_[abcd]/i', memname)   /* pattern for tables named adc_<digits>_<single letter a, b, c, or d> */
  ;

proc datasets nolist lib=work;
  delete &tables;
quit;

%symdel tables;

Re: Finding a series if the "number" is in the middle of a variable

andreas_lds — Wed, 26 Aug 2020 13:28:49 GMT

Not clear what you don't want to delete. Is deleting anything starting with adc_ acceptable? If yes, use adc_: in the delete statement. If not, please define the rules identifying a dataset that should be deleted.

Re: Finding a series if the "number" is in the middle of a variable

sam_sas2 — Wed, 26 Aug 2020 13:45:02 GMT

What's the purpose of first 3 lines?

Re: Finding a series if the "number" is in the middle of a variable

sam_sas2 — Wed, 26 Aug 2020 13:46:51 GMT

No, so I want to delete all datasets that have a number in between. I have datasets like adc_ab which I shouldn't delete. Hope this gives more clarity.

Re: Finding a series if the "number" is in the middle of a variable

Tom — Wed, 26 Aug 2020 13:52:26 GMT

@sam_sas2 wrote:
No, so I want to delete all datasets that have a number in between. I have datasets like adc_ab which I shouldn't delete. Hope this gives more clarity.

SAS has an ANYDIGIT() function.

Re: Finding a series if the "number" is in the middle of a variable

RichardDeVen — Wed, 26 Aug 2020 14:00:26 GMT

The first three lines creates some sample data sets for the example code to work with.

Re: Finding a series if the "number" is in the middle of a variable

s_lassen — Wed, 26 Aug 2020 16:38:20 GMT

You might try SQL SELECT INTO, with a Pearl Regular Expression to find the datasets you are interested in (or not, actually):

proc sql noprint;
  select memname into :ToBeDeleted separated by ' ' from dictionary.tables
  where libname='HMP' and prxmatch('/adc_\d+_./i',memname);
quit;

And then use the generated macro variable in PROC DATASETS:

proc datasets nolist lib=hmp;
delete &ToBeDeleted;
run;quit;