How to set most datasets in a library but except for a few datasets

[Sally_Caffrey]

Hi,

I have many datasets in a library with same name prefix like x_. I want to set most of them into one dataset, however except for a few datasets like x_11 and x_23. 

My code is :

data want;

    set x_:;

run;

How to exclude x_11 and x_23 from set statement ?

[PaigeMiller]

I can think of two ways, the first may not be the fastest since it reads all data sets and then discard records from the unwanted data sets.

data want;
    set x_: indsname=indsname;
    if indsname =:'WORK.X_11' or indsname=:'WORK.X_23' then delete;
run;

The second way is to create a macro variable which lists all the data sets except the ones you don't want, then use the macro variable in the SET statement.

proc sql;
    select memname into :names separated by ' ' from dictionary.tables 
    where libname="WORK" and memname eqt 'X_' and memname net 'X_11' and memname net 'X_23';
quit;
data want;
    set &names;
run;

[Sally_Caffrey]

Thanks a lot! Since I only have ~50 datasets to combine, so the speed is not a problem. I tried the first way and it works after a little change:

data want;
    set x_: indsname=indsname;
    if scan(indsname,1,'.') ='WORK' and scan(indsname,2,'.') in ('X_11' 'X_23') then delete;
run;

😀

[PaigeMiller]

May I ask why the change was needed? It seems like the code I wrote ought to work unchanged.

[Sally_Caffrey]

Sorry I write the code incorrecly just now. Your code is correct. 🙂 May I ask why use =: ？The result does not change if remove ':'

[PaigeMiller]

---

@Sally_Caffrey wrote:
Sorry I write the code incorrecly just now. Your code is correct. 🙂 May I ask why use =: ？The result does not change if remove ':'

---

In your original problem statement, where you used 

set x_:;

it wasn't clear to me if the data set names were X_1 X_2 X_3 and so on, or could there also be X_1a X_2a X_3b. So the =: (equal-colon) was used to find all data set names that begin with X_. That's what the =: does, it looks for strings that begin with whatever is in the quotes to the right of the =:

It would appear that you don't have anything other than numbers after X_ and so the colon is not needed.

I also realize now that if you have X_233, then this will not get selected as anything that begins with X_23 is not selected. So really, a more complete specification of the problem would have helped.

[Patrick]

Below how that's often done.

proc sql noprint;
  select cats(libname,'.',memname) into :memlist separated by ' '
  from dictionary.tables
  where 
    libname = 'WORK' and 
    memname not in ('X_11','X_23')
  ;
quit;

data want;
  set &memlist;
run;

[Sally_Caffrey]

Hi Patrick, thank you! This method works for me! It is great that it gives libname in the macro name so I can set dataset in any library.

[ballardw]

If all your data sets are sequentially numbered something like this should work:

data want;
   set x_1 - x_10  x_12-x_22 x_24-x_<whatever the last number may be>
   ;
run;

One concern though, how sure are you that all variables with the same names are of the same type or have the same lengths?

---

@Sally_Caffrey wrote:

Hi,

I have many datasets in a library with same name prefix like x_. I want to set most of them into one dataset, however except for a few datasets like x_11 and x_23. 

My code is :

data want;

    set x_:;

run;

How to exclude x_11 and x_23 from set statement ?

---

[Sally_Caffrey]

Hi Ballardw, the variables in all of datasets are created using one blank dataset with specific variable names, in wihch the variables format have been set up.