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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
KachiM
Rhodochrosite | Level 12
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;

View solution in original post

15 REPLIES 15
KachiM
Rhodochrosite | Level 12
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
Meteorite | Level 14

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
Rhodochrosite | Level 12

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

Ronein
Meteorite | Level 14

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
Meteorite | Level 14

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
Meteorite | Level 14

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

KachiM
Rhodochrosite | Level 12

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
Meteorite | Level 14

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
Tourmaline | Level 20

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
Meteorite | Level 14
Yes. Keep only variables that contain 3 in their names
KachiM
Rhodochrosite | Level 12

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
Super User
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
Ammonite | Level 13

@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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 2711 views
  • 9 likes
  • 6 in conversation