topic Re: Dele columns by criteria related to its names in SAS Programming

Dele columns by criteria related to its names

Ronein — Thu, 07 Oct 2021 06:31:10 GMT

Hello

I have a data set with many columns .

I want to remove columns by the following rule:

IF name of the variables end with a number greater then 3 then need to delete this column.

So, in this example I will remove columns X4,X5, W4,W5

What is the way to do it please?

Data tbl;
input ID x1 x2 x3 x4 x5 w1 w2 w3 w4 w5 ;
cards;
1 10 20 30 40 50 11 12 13 14 15
2 20 30 40 50 60 12 13 14 15 16
;
Run;

Re: Dele columns by criteria related to its names

Patrick — Thu, 07 Oct 2021 07:01:25 GMT

Below one way how you could go about this.

Data tbl;
  input ID x1 x2 x3 x4 x5 w1 w2 w3 w4 w5;
cards;
1 10 20 30 40 50 11 12 13 14 15
2 20 30 40 50 60 12 13 14 15 16
;

%let droplist=;
proc sql noprint;
  select 
    name into :droplist separated by ' '
  from dictionary.columns
  where 
    libname='WORK' 
    and memname='TBL' 
    and input(scan(name,-1,,'kd'),32.)>3
  ;
quit;

data tbl_new;
  set tbl;
  drop &droplist;
run;

Re: Dele columns by criteria related to its names

Kurt_Bremser — Thu, 07 Oct 2021 07:14:58 GMT

Catch the opportunity and restructure to a more usable long layout:

Data tbl;
input ID x1 x2 x3 x4 x5 w1 w2 w3 w4 w5 ;
cards;
1 10 20 30 40 50 11 12 13 14 15
2 20 30 40 50 60 12 13 14 15 16
;

data want;
set tbl;
array _x{*} x:;
array _w{*} w:;
do i = 1 to min(3,dim(_x));
  x = _x{i};
  w = _w{i};
  output;
end;
keep id i x w;
run;

Re: Dele columns by criteria related to its names

ballardw — Thu, 07 Oct 2021 12:47:42 GMT

Data want;
   set have (keep=id x1-x3 w1-w3);
run;

Or were you asking because you have no idea what the actual names of the variables involved will be?