topic Re: How Do I Delete column(variable) conditinally in SAS Data Management

How Do I Delete column (variable) conditionally

507A — Thu, 14 Dec 2017 19:17:05 GMT

Hello

I have datasets, with 20 rows and 51 columns. and I need to delete column(variable) if it has contain specific string.

The string is 'NO'

dataset (variable name: A, P1, P2, P3, ...... P50)

A P1 P2 P3 ..... P50

0 AP AP AP

1 30 25 80

2 50 30 120

3 NO 50 NO

4 NO 20 NO

I want to using array statment or macro but I don't have Idea to succesfully solve this problem.

I know this is so weired request but I have to do.

How can I do that?

Re: How Do I Delete column(variable) conditinally

stat_sas — Fri, 06 May 2016 00:40:28 GMT

Below is one approach using sql and data steps:

data have;
input P1 $ P2 $ P3 $;
datalines;
AP AP AP
30 25 80
50 30 120
NO 50 NO
NO 20 NO
;

data want(keep=vname values);
set have;
array p(*) P1-P3;
do i=1 to dim(p);
vname=vname(p(i));
values=p(i);
output;
end;
run;

proc sql;
select distinct vname into :del separated by ' ' from want
where values='NO';
quit;

data final;
set have(drop=&del);
run;

Re: How Do I Delete column(variable) conditinally

Astounding — Fri, 06 May 2016 00:48:59 GMT

So you do realize then that when you delete the column, the entire column goes. Not just the NO values, but all the values.

Re: How Do I Delete column(variable) conditinally

Haikuo — Fri, 06 May 2016 01:14:09 GMT

Proc sql maybe not necessary if 1X data step can get the macro variable:

data have;
input P1 $ P2 $ P3 $;
datalines;
AP AP AP
30 25 80
50 30 120
NO 50 NO
NO 20 NO
;
 
data _null_;
set have end=done;
array p(*) P1-P3;
array _name(3) $2 _temporary_ ;
do i=1 to dim(p);
if p(i)='NO' then _name(i)=vname(p(i));
end;
if done then call symputx ('del',catx(' ', of _name(*)));
run;


data final;
set have(drop=&del);
run;

Re: How Do I Delete column(variable) conditinally

Ksharp — Fri, 06 May 2016 01:19:11 GMT

data have;
input P1 $ P2 $ P3 $;
datalines;
AP AP AP
30 25 80
50 30 120
NO 50 NO
NO 20 NO
;
run;
data _null_;
 set sashelp.vcolumn(keep=libname memname name type 
    where=(libname='WORK' and memname='HAVE' and type='char')) end=last;
 if _n_ eq 1 then call execute('proc sql;create table temp as select');
 call execute(catx(' ','sum(',name,'="NO") as',name));
 if last then call execute('from have;quit;');
  else call execute(',');
run;
proc transpose data=temp out=temp1;run;
proc sql;
 select _name_ into : list separated by ' '
  from temp1
   where col1=0;
quit;
data want;
 set have;
 keep &list;
run;