SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How Do I Delete column(variable) conditinally

Reply
Learner
Posts: 1

How Do I Delete column(variable) conditinally

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

.

.

.

20 

 

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?

 

Trusted Advisor
Posts: 1,204

Re: How Do I Delete column(variable) conditinally

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;

Respected Advisor
Posts: 3,124

Re: How Do I Delete column(variable) conditinally

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;
Super User
Posts: 5,096

Re: How Do I Delete column(variable) conditinally

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

Super User
Posts: 9,691

Re: How Do I Delete column(variable) conditinally

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;
Ask a Question
Discussion stats
  • 4 replies
  • 1116 views
  • 3 likes
  • 5 in conversation