select variables having a particular string as part of its name

[Ronein]

Hello

I have a data set called tbl1.

I want to select variables having a particular string as part of its name.

for example: I want to select all variables that contain "3" (so need to select  X_3 ,W_3)

What is the way to do it please? 

Data tbl1;
input ID x_1 X_2 X_3 Y_1 Y_2 W_3;
cards;
1 2 3 4 5 6 7
2 3 4 5 6 7 8 
;
run;

[KachiM · Accepted solution]

Data have;
input ID x_1 X_2 X_3 Y_1 Y_2 W_3;
cards;
1 2 3 4 5 6 7
2 3 4 5 6 7 8 
;
run;

data _null_;
   length List $ 32767;
   set have;
   array v X_1 -- W_3;
   if _N_ = 1 then do;
      do i = 1 to dim(v);
         if substr(vname(v[i]),3,1) = '3' then List = catx(' ',List, vname(v[i]));
      end;
      call symputx('List', List);
   end;
stop;   
run;

%put &List;

data want;
   set have;
keep ID &List;   
run;

proc print data = want;
run;

[Ronein]

Great and thank you!

Can i ask if there is another way to write  the statement     array v    X_1 -- W_3;

Instead of writing   X_1 -- W_3  ,Can I write _ALL_??

I want that the array will contain all columns without specifying their names 

[KachiM]

_ALL_ will take all variables including ID into the Array. Please check it by testing for your application.

[Ronein]

When I run with _ALL_ I receive an error

ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.

Data have;
input ID x_1 X_2 X_3 Y_1 Y_2 W_3;
cards;
1 2 3 4 5 6 7
2 3 4 5 6 7 8 
;
run;

data cc;
   length List $ 32767;
   set have;
   array  v   _ALL_;
   if _N_ = 1 then do;
      do i = 1 to dim(v);
         if substr(vname(v[i]),3,1) = '3' then List = catx(' ',List, vname(v[i]));
      end;
      call symputx('List', List);
   end;
stop;   
run;
%put &List;


data want;
set have;
array vn &List;
keep ID &List;   
run;

[Ronein]

I found solution.

Data have;
input ID x_1 X_2 X_3 Y_1 Y_2 W_3;
cards;
1 2 3 4 5 6 7
2 3 4 5 6 7 8 
;
run;

data bbb;
   length List $ 32767;
   set have;
   array  v   _numeric_;
   if _N_ = 1 then do;
      do i = 1 to dim(v);
         if substr(vname(v[i]),3,1) = '3' then List = catx(' ',List, vname(v[i]));
      end;
      call symputx('List1', List);
   end;
stop;   
run;
%put &List1;


data ccc;
   length List $ 32767;
   set have;
   array  v   _character_;
   if _N_ = 1 then do;
      do i = 1 to dim(v);
         if substr(vname(v[i]),3,1) = '3' then List = catx(' ',List, vname(v[i]));
      end;
      call symputx('List2', List);
   end;
stop;   
run;
%put &List2;



data want;
set have;
array vn &List;
keep ID &List1 &List2;   
run;

[Ronein]

What is the purpose of the statement  "array vn &List;"?

[KachiM]

Yes, the array statement is not required. I realized it and edited my post in the next minute. You are using the first post. Also, the length of the variables are supposed to be of length not less than 3. Since, ID has a length of 2 and so when SUBSTR() function is used it looks for the third character but it doen't have it. So you got error messages.

[Ronein]

Hello

What is wrong with the code below.

I want to keep names of columns that contain 3.

Data have;
input ID x_1 X_2 X_3 Y_1 Y_2 W_3 RRRRR_3 _3T;
cards;
1 2 3 4 5 6 7 9 5
2 3 4 5 6 7 8 8 4 
;
run;

%let field=3;

data bbb;
   length List $ 32767;
   set have;
   array  v   _numeric_;
   if _N_ = 1 then do;
      do i = 1 to dim(v);
         if vname(v[i])) LIKE "%&field.%" then List = catx(' ',List, vname(v[i]));
      end;
      call symputx('List1', List);
   end;
stop;   
run;
%put &List1;

[PeterClemmensen]

What do you mean by "keep names of columns that contain 3"? Do you want to keep those variables only and drop all other variables?

If so, then do something like this

proc sql noprint;
   select name into :vars separated by ' '
   from dictionary.columns
   where libname='WORK' & memname='HAVE' & find(name, '3') ne 0;
quit;

data want;
   set have;
   keep &vars.;
run;

[Ronein]

Yes. Keep only variables that contain 3 in their names

[Kurt_Bremser]

I merged your two posts concerning the same issue.

[KachiM]

ID is numeric variable as other variables. But length of Variable ID is 2 whereas others have a length of 3 or more. Change ID as ID1 so that it has a length of 3.

input ID1 x_1 X_2 X_3 Y_1 Y_2 W_3 RRRRR_3 _3T;

Also change the statement as:

         *if vname(v[i]) LIKE "%&field.%" then List = catx(' ',List, vname(v[i]));
         if substr(vname(v[i]),3,1) = &field then List = catx(' ',List, vname(v[i]));

[Ksharp]

Data tbl1;
input ID x_1 X_2 X_3 Y_1 Y_2 W_3;
cards;
1 2 3 4 5 6 7
2 3 4 5 6 7 8 
;
run;
proc transpose data=tbl1(obs=0) out=temp;
var _all_;
run;
proc sql noprint;
select _name_ into : names separated by ' '
 from temp
  where _name_ contains '3';
quit;
data want;
 set tbl1;
 keep id &names;
run;

[hashman]

@Ronein :

1. In general, you can have both numeric and character variables, the names of both having to be accounted for. 

2. Using the dynamic properties of the hash object, it can be done in a single step.

data have ;                                                     
  input ID :$3. X_1 X_2 X_3 Y_1 Y_2 W_3 :$1. ;                  
  cards ;                                                       
ID1 1 2 3 4 5 6 C                                               
ID2 2 3 4 5 6 7 D                                               
run ;                                                           
                                                                
data _null_ ;                                                   
  if _n_ = 1 then do ;                                          
    dcl hash h (ordered:"A") ;                                  
    h.definekey  ("_n_") ;                                      
    h.definedata ("_n_") ;                                      
    if 0 then set have ;                                        
    array nn _numeric_ ;                                        
    array cc _char_ ;                                           
    do over nn ;                                                
      if find (vname (nn), "3") then h.definedata (vname (nn)) ;
    end ;                                                       
    do over cc ;                                                
      if find (vname (cc), "3") then h.definedata (vname (cc)) ;
    end ;                                                       
    h.definedone() ;                                            
  end ;                                                         
  set have end = z ;                                            
  h.add() ;                                                     
  if z then h.output (dataset:"want (drop = _:)") ;             
run ;                                                           

This way, if none of the variable names contain "3", the output data set will have no variables (that's why _N_ is added as a data variable and dropped in the output).

Kind regards

Paul D.