DATA Step, Macro, Functions and more

Drop column if [value]

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Drop column if [value]

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.


Accepted Solutions
Solution
‎06-19-2015 10:03 AM
Trusted Advisor
Posts: 1,137

Re: Drop column if [value]

Posted in reply to problems99

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

Thanks,
Jag

View solution in original post


All Replies
Valued Guide
Posts: 860

Re: Drop column if [value]

Posted in reply to problems99

will you define 'reference line'?

Contributor
Posts: 29

Re: Drop column if [value]

Posted in reply to problems99

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

Solution
‎06-19-2015 10:03 AM
Trusted Advisor
Posts: 1,137

Re: Drop column if [value]

Posted in reply to problems99

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

Thanks,
Jag
Contributor
Posts: 29

Re: Drop column if [value]

Posted in reply to Jagadishkatam

Thanks it works well.

Super User
Posts: 5,509

Re: Drop column if [value]

Posted in reply to problems99

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 295 views
  • 1 like
  • 4 in conversation