Columsn to rows

[SASPhile]

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 

[Mit]

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

[Jagadishkatam]

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

[SASPhile]

whats the variable in here?

[Jagadishkatam]

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

[art297]

How about?:

proc transpose data=have  out=want

(rename=(col1=addr rowsid=id)

  where=(not missing(addr))

  drop=_:);

  by rowsid notsorted;

  var home_addr bill_addr  work_addr;

run;

[Astounding]

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;

[Reeza]

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;