BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Billy_Pilgrim
Fluorite | Level 6

I want to delete some data sets from a common library which have neither the same prefix nor suffix, but which share a common naming convention. Specifically, I have a number of datasets which look like *_name_* where "_name_" is some description that is common to all the data sets and * is a wildcard character.

 

I am aware of the fact that if my data had a common prefix, e.g. name_*, then I could delete all of the data sets with proc data sets and the colon operator like so

 

proc datasets library=library nolist;
delete name_:;
run;

I guess what I am looking for is an analogous solution for my situation, for example

 

proc datasets library=library nolist;
delete :_name_:;
run;

Unfortunately, the latter does not seem to work.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

You could use PROC SQL to create a macro variable with the data set names of interest

 

proc sql noprint;
     select memname into :dsnames separated by ' ' from dictionary.tables
     where find(upcase(memname),'_NAME_')>0 and libname='WORK';
quit;
--
Paige Miller

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

Retrieve the list of dataset names first to macro variable:

proc sql noprint;
select catx(".",libname,memname) into :datasets separated by " "
from dictionary.tables
where libname = "LIBRARY" and index(memname,"_NAME_");
quit;

proc delete data=&datasets.;
run;
Billy_Pilgrim
Fluorite | Level 6

Many thanks, this nearly worked for me, but then I get the following error:

 

Error: sqxexpr: opcode 342 not implemented

 

I think caused by the index() portion...anyway, could just be my sas implementation. The core idea certainly works, however.

Kurt_Bremser
Super User

Hmm.

Just ran this on On Demand, successfully:

proc sql noprint;
select catx(".",libname, memname) into :datasets separated by " "
from dictionary.tables
where libname = "SASHELP" and index(memname,"BU");
quit;

%put &datasets;

Log:

 69         proc sql noprint;
 70         select catx(".",libname, memname) into :datasets separated by " "
 71         from dictionary.tables
 72         where libname = "SASHELP" and index(memname,"BU");
 73         quit;
 NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
       real time           0.01 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
       memory              5796.46k
       OS Memory           29860.00k
       Timestamp           29.09.2021 12:25:16 nachm.
       Step Count                        24  Switch Count  0
       Page Faults                       0
       Page Reclaims                     443
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 74         
 75         %put &datasets;
 SASHELP.BURROWS SASHELP.BUY SASHELP.VCNTABU
Billy_Pilgrim
Fluorite | Level 6

Yes, unfortunately, that doesn't seem to work for me. I'm not sure how to increase the verbosity of the logging unfortunately, so there is not much in the log beyond the error.

 

1426 proc sql noprint;
1427 select catx(".",libname, memname) into :datasets separated by " "
1428 from dictionary.tables
1429 where libname = "SASHELP" and index(memname,"BU");
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
Error: sqxexpr: opcode 342 not implemented.
NOTE: No rows were selected.
1430 quit;

PaigeMiller
Diamond | Level 26

@Billy_Pilgrim wrote:

Many thanks, this nearly worked for me, but then I get the following error:

 

Error: sqxexpr: opcode 342 not implemented

 

I think caused by the index() portion...anyway, could just be my sas implementation. The core idea certainly works, however.


 

Providing ERROR messages without showing us the rest of the LOG does not help. Please show us the entire LOG for your code.

--
Paige Miller
PaigeMiller
Diamond | Level 26

You could use PROC SQL to create a macro variable with the data set names of interest

 

proc sql noprint;
     select memname into :dsnames separated by ' ' from dictionary.tables
     where find(upcase(memname),'_NAME_')>0 and libname='WORK';
quit;
--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 692 views
  • 2 likes
  • 3 in conversation