DATA Step, Macro, Functions and more

get required column names

Reply
Frequent Contributor
Posts: 84

get required column names

Hi,

I want all the 'N' varibales and their id's

data have;

input ID AAA$ BBB$ CCC$ DDD$ ;

cards;

11 Y N Y N

12 Y Y N Y

13 N N N Y

14 N N N N

run;

want:

11 BBB

12 CCC

13 AAA BBB CCC

14 AAA BBB CCC DDD

Respected Advisor
Posts: 3,799

Re: get required column names

data have;
   input ID (AAA BBB CCC DDD)(:$1.);
   cards;
11 Y N Y N
12 Y Y N Y
13 N N N Y
14 N N N N
;;;;
   run;
proc print;
  
run;
proc transpose name=vid out=have2(where=(col1='N'));
   by id;
   var aaa--ddd;
   run;
proc print;
  
run;
proc transpose out=want(drop=_Smiley Happy;
   by id;
   var vid;
   run;
proc print;
  
run;
6-10-2015 2-29-47 PM.png
Trusted Advisor
Posts: 1,137

Re: get required column names

Please also try alternativey

data want;

set have;

array new(4) AAA BBB CCC DDD;

do i = 1 to 4;

if new(i)='N' then new2=vname(new(i));

if new2 ne '' then output;

end;

run;

proc sort data=want nodupkey;

by id new2;

run;

proc transpose data=want out=trans(drop=_name_);

by id;

var new2;

run;

Thanks,

Jag

Thanks,
Jag
Valued Guide
Posts: 860

Re: get required column names

This might get you what you want.  I put the dummy (id = 10) in their to keep the columns in order for the final output.  Not exactly the presentation you have but it's close.

data have;

input ID AAA$ BBB$ CCC$ DDD$ ;

cards;

10 N N N N

11 Y N Y N

12 Y Y N Y

13 N N N Y

14 N N N N

;

run;

proc transpose data=have out=prep(where=(col1 = 'N'));by id;var aaa bbb ccc ddd;

proc transpose data=prep out=prep2(drop=_label_ _name_);by id;var _NAME_;

data want;

set prep2;

if id = 10 then delete;

run;

Super Contributor
Posts: 275

Re: get required column names

data want;

   set have;

   array vars aaa--ddd;

   length temp $20.;

   do i=1 to dim(vars);

      if vars(i)='N' then do;

         temp=catx(' ',temp,vname(vars(i)));

         n=count(catx(' ',of vars(*)),'N');

   end;   

   end;

   call missing(of vars(*));

   do j=1 to n;

   vars(j)=scan(temp,j);

   end;

  keep id aaa--ddd;

run;

Ask a Question
Discussion stats
  • 4 replies
  • 240 views
  • 0 likes
  • 5 in conversation