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
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;
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 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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.