DATA Step, Macro, Functions and more

Columsn to rows

Reply
Super Contributor
Posts: 673

Columsn to rows

o rowsid  home_addr       bill_addr          work_addr
1   abc street      
2   pqr street                                 mno street
3   Main st          zxc street        Downtown

how to make them look like this: 

id    Addr
1     abc street
2     pqr street
2     mno street
3     Main street
3     Zxc street
3     Downtown 

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Columsn to rows

proc sort data=have;
     by id;
run;

proc transpose data=BOOK12  out=have1(drop=_name_);
     by id;
     var home_addr bill_addr  work_addr;
run;


data want;
set have1;
     if col1='' then delete;
     rename col1=Addr ;
run;


id


Addr


1


abc street


2


pqr street


2


mno street


3


Main st


3


zxc street


3


Downtown

Trusted Advisor
Posts: 1,137

Re: Columsn to rows

Alternatively by arrays

data want(where=(addr ne ''));

    set have;

    array cha(3) $ ("home_addr","bill_addr","work_addr");

    array new(3) $     home_addr  bill_addr  work_addr;

    do i = 1 to dim(cha);

    variable=cha(i);

    Addr=new(i);

    output;

    end;

    keep  rowsid  addr;

run;

Thanks,

Jagadish

Thanks,
Jag
Super Contributor
Posts: 673

Re: Columsn to rows

Posted in reply to Jagadishkatam

whats the variable in here?

Trusted Advisor
Posts: 1,137

Re: Columsn to rows

The variable "variable" here has variables (home_addr,bill_addr,work_addr) as values.

I mean the horizontal variables are converted to the values in the column variable.

Thanks,

Jagadish

Thanks,
Jag
PROC Star
Posts: 7,468

Re: Columsn to rows

How about?:

proc transpose data=have  out=want

(rename=(col1=addr rowsid=id)

  where=(not missing(addr))

  drop=_Smiley Happy;

  by rowsid notsorted;

  var home_addr bill_addr  work_addr;

run;

Super User
Posts: 5,498

Re: Columsn to rows

While you have already received good advice ... if you would prefer a simple approach, here is one way:

data want;

   set have;

   if home_addr > ' ' then do;

      addr = home_addr;

      id = rowsid;

      output;

   end;

   if work_addr > ' ' then do;

      addr = work_addr;

      id = rowsid;

      output;

   end;

   if bill_addr > ' ' then do;

      addr = bill_addr;

      id = rowsid;

      output;

   end;

   keep id addr;

run;


Super User
Posts: 19,770

Re: Columsn to rows

Here's a data step solution:

data want;

set have;

*Declare array of all addresses;

array addr(3) $ home_add work_addr bus_addr;

*loop over address to output;

do i=1 to dim(addr);

*if address is not missing then assign to variable and output;

if addr(i) ne " " then do;

address=addr(i);

output;

end; *end do;

end; *end do loop;

keep rowid address;

run;

Ask a Question
Discussion stats
  • 7 replies
  • 375 views
  • 0 likes
  • 6 in conversation