topic Re: Columsn to rows in SAS Programming

Columsn to rows

SASPhile — Thu, 07 Nov 2013 04:06:46 GMT

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

Re: Columsn to rows

Mit — Thu, 07 Nov 2013 04:31:07 GMT

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;

Addr

abc street

pqr street

mno street

Main st

zxc street

Downtown

Re: Columsn to rows

Jagadishkatam — Thu, 07 Nov 2013 10:38:40 GMT

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

Re: Columsn to rows

SASPhile — Mon, 11 Nov 2013 17:34:23 GMT

whats the variable in here?

Re: Columsn to rows

art297 — Mon, 11 Nov 2013 18:30:50 GMT

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;

Re: Columsn to rows

Astounding — Mon, 11 Nov 2013 19:44:02 GMT

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;

Re: Columsn to rows

Jagadishkatam — Tue, 12 Nov 2013 00:49:18 GMT

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

Re: Columsn to rows

Reeza — Tue, 12 Nov 2013 01:02:02 GMT

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;