data transformation: multiple columns to single column

[jkf91]

Hi all,

I want to do the following:

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

to

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

[art297 · Accepted solution]

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;

[Linlin]

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