topic Re: select variables having a particular string as part of its name in SAS Programming

select variables having a particular string as part of its name

Ronein — Sun, 07 Jul 2019 07:57:51 GMT

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;

Re: select variables having a particular string as part of its name

KachiM — Sun, 07 Jul 2019 08:20:05 GMT

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;

Re: select variables having a particular string as part of its name

Ronein — Sun, 07 Jul 2019 08:24:59 GMT

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

Re: select variables having a particular string as part of its name

KachiM — Sun, 07 Jul 2019 08:30:59 GMT

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

Re: select variables having a particular string as part of its name

Ronein — Sun, 07 Jul 2019 09:56:57 GMT

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;

Re: select variables having a particular string as part of its name

Ronein — Sun, 07 Jul 2019 09:59:33 GMT

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;

Re: select variables having a particular string as part of its name

Ronein — Sun, 07 Jul 2019 10:00:46 GMT

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

select variables contain a particular string

Ronein — Sun, 07 Jul 2019 10:21:56 GMT

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;

Re: select variables contain a particular string

PeterClemmensen — Sun, 07 Jul 2019 10:29:00 GMT

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;

Re: select variables having a particular string as part of its name

KachiM — Sun, 07 Jul 2019 10:30:02 GMT

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.

Re: select variables contain a particular string

Kurt_Bremser — Sun, 07 Jul 2019 11:06:59 GMT

I merged your two posts concerning the same issue.

Re: select variables contain a particular string

Ronein — Sun, 07 Jul 2019 11:13:19 GMT

Yes. Keep only variables that contain 3 in their names

Re: select variables having a particular string as part of its name

Ksharp — Sun, 07 Jul 2019 11:27:27 GMT

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;

Re: select variables contain a particular string

KachiM — Sun, 07 Jul 2019 14:49:01 GMT

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]));

Re: select variables having a particular string as part of its name

hashman — Mon, 08 Jul 2019 02:14:46 GMT

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

Re: select variables having a particular string as part of its name

hashman — Tue, 09 Jul 2019 03:42:07 GMT

Another way of achieving the goal in a single step is using CALL VNEXT instead of the dual arrays (as suggested by @mkeintz on SAS-L) coupled with CALL EXECUTE:

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_ ;                                   
  set have (obs = 1) ;                          
  length _vn $ 32 ;                              
  call execute ("data want; set have(keep=") ;  
  do until (cmiss (_vn)) ;                      
    call vnext (_vn) ;                          
    if find (_vn, "3") then call execute (_vn) ;
  end ;                                         
  call execute ("); run;") ;                    
run ;

Kind regards

Paul D.