Drop column if [value]

[problems99]

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.

[Steelers_In_DC]

will you define 'reference line'?

[problems99]

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

[Jagadishkatam · Accepted solution]

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

[problems99]

Thanks it works well.

[Astounding]

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;

end;

call execute('run;');

stop;

run;

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

Good luck.