topic data transformation: multiple columns to single column in SAS Procedures

data transformation: multiple columns to single column

jkf91 — Sun, 22 Jan 2012 22:41:45 GMT

Hi all,

I want to do the following:

Index	ZCTA
1	35010
2	36830	36831	36832	36849
3	36507	36509
4	36502

Index	ZCTA
1	35010
2	36830
2	36831
2	36832
2	36849
3	36507
3	36509
4	36502

I have some rows with over 90 columns.

I appreciate your help in advance!

Thanks,

Chris

data transformation: multiple columns to single column

art297 — Sun, 22 Jan 2012 23:38:47 GMT

If all of your columns (except for index) are called zctaX (where x is an incremental number), and your data file is called 'have', then the following will do what you want:

data have;

input Index ZCTA1-ZCTA4;

cards;

1 35010 . . .

2 36830 36831 36832 36849

3 36507 36509 . .

4 36502 . . .

;

proc transpose data=have

out=want (rename=(col1=zcta) where=(not missing(zcta)));

by index;

var ZCTA:;

run;

data transformation: multiple columns to single column

Linlin — Sun, 22 Jan 2012 23:41:22 GMT

data have;

infile datalines missover;

input index zcta z1-z3;

datalines;

1 35010

2 36830 36831 36832 36849

3 36507 36509

4 36502

run;

data want(keep=index zcta);

set have;

array _n(*) zcta z1-z3;

do i=1 to dim(_n) while (_n(i) ne .);

zcta=_n(i);

output;

end;

run;

option nocenter;

proc print;run;

Obs index zcta

1 1 35010

2 2 36830

3 2 36831

4 2 36832

5 2 36849

6 3 36507

7 3 36509

8 4 36502

Linlin