topic Drop column if [value] in SAS Programming

Drop column if [value]

problems99 — Fri, 19 Jun 2015 13:13:30 GMT

Hello,

I would like to remove a variable if there is no observation in the reference line of my datasets (in this exemple, it is a='a').

Here is my test data:

data test3;
input a $ var1 var2 var3 var4 var5;
datalines;
a 1 . 2 . 4 
b . . 1 2 3 
c 1 2 3 4 5 
;

I would like an output like that:

a 1 2 4

b . 1 3

c 1 3 5

So I create a macro with the name of all my variables that start by 'var'

proc datasets nolist library = work memtype = data; 
 contents data = work.test3 out = structure3 noprint;
 run;
quit;

data _null_;
    set structure3;
 retain taeller 0;
 if length(name) > 2 then
   do;
  if substr(lowcase(name),1,3) = "var" then
   do;
  taeller = taeller + 1;
  call symputx('taeller',taeller,'G');
  call symputx('vbr' !! trim(left(put(taeller,5.))), trim(left(name)), 'G');
      end;
   end;
run;

For now, everything is okay. So I modify test3 as I want.

%macro testt2;
%local i;
%do i=1 %to &taeller;
 data test3;
 set test3;
 if 'a'=a then do;
 if &&vbr&i=. then drop &&vbr&i;
 end;
 run;
%end;
%mend testt2;
%testt2;

The problem is in red. SAS does not allow this syntax. If I use do end and set the red words into it all my variables are dropped.

Thanks for your help.

Re: Drop column if [value]

Steelers_In_DC — Fri, 19 Jun 2015 13:25:42 GMT

will you define 'reference line'?

Re: Drop column if [value]

problems99 — Fri, 19 Jun 2015 13:28:42 GMT

In my real application, a is a date and 'a' a particular date.

Re: Drop column if [value]

Jagadishkatam — Fri, 19 Jun 2015 14:03:13 GMT

Please try

data test3;

input a $ var1 var2 var3 var4 var5;

datalines;

a 1 . 2 . 4

b . . 1 2 3

c 1 2 3 4 5

;

proc transpose data=test3 out=test4;

by a;

var var1-var5;

run;

proc sql;

select _name_ into :vars separated by ' ' from test4 where col1=. and a='a';

quit;

%put &vars;

data test3;

set test3;

drop &vars;

run;

Thanks,

Jag

Re: Drop column if [value]

problems99 — Fri, 19 Jun 2015 14:12:39 GMT

Thanks it works well.

Re: Drop column if [value]

Astounding — Fri, 19 Jun 2015 14:13:32 GMT

I'm not sure this is simpler, but it is (in a way) just one step:

data _null_;

set have;

where a='a';

call execute('data want; set have;');

array vars {*} var1-var5;

do _i_=1 to dim(vars);

if vars{_i_}=. then do;

name = vname(vars{_i_});

call execute ('drop ' || name || ';');

end;

call execute('run;');

stop;

run;

This program assumes there is only one observation meeting the WHERE condition;

Good luck.