Hello,
I am looking to transpose the below data as shown in the output. Any help is appreciated. Thanks!!
data rt;
input zip $5. empid 8.;
cards;
19701 8798
19701 5489
18054 3578
78945 3157
19701 7845
18045 2245
;
Run;
Output:
Zip | empid1 | empid2 | empid3 |
19701 | 8798 | 5489 | 7845 |
18054 | 3578 | 2245 | |
78945 | 3157 |
Set a counter for use in proc transpose:
data rt;
input zip $5. empid 8.;
cards;
19701 8798
19701 5489
18054 3578
78945 3157
19701 7845
18054 2245
;
run;
proc sort data=rt;
by zip;
run;
data rt1;
set rt;
by zip;
if first.zip
then count = 1;
else count + 1;
run;
proc transpose
data=rt1
out=want (drop=_name_)
prefix=empid
;
by zip;
id count;
var empid;
run;
Set a counter for use in proc transpose:
data rt;
input zip $5. empid 8.;
cards;
19701 8798
19701 5489
18054 3578
78945 3157
19701 7845
18054 2245
;
run;
proc sort data=rt;
by zip;
run;
data rt1;
set rt;
by zip;
if first.zip
then count = 1;
else count + 1;
run;
proc transpose
data=rt1
out=want (drop=_name_)
prefix=empid
;
by zip;
id count;
var empid;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.