Help using Base SAS procedures

data transformation: multiple columns to single column

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

data transformation: multiple columns to single column

Hi all,

I want to do the following:

IndexZCTA
135010


236830368313683236849
33650736509

436502


to

IndexZCTA
135010
236830
236831
236832
236849
336507
336509
436502

I have some rows with over 90 columns.

I appreciate your help in advance!

Thanks,

Chris


Accepted Solutions
Solution
‎01-22-2012 06:38 PM
PROC Star
Posts: 7,363

data transformation: multiple columns to single column

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;

View solution in original post


All Replies
Solution
‎01-22-2012 06:38 PM
PROC Star
Posts: 7,363

data transformation: multiple columns to single column

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;

Super Contributor
Posts: 1,636

data transformation: multiple columns to single column

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1040 views
  • 4 likes
  • 3 in conversation