transpose data long to wide

[vicky07]

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

[Reeza]

Did you try a PROC TRANSPOSE?

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

PS you have a typo in your sample data, 18045 and 18054 should probably both be the same ID.

[Kurt_Bremser · Accepted solution]

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;

[Reeza]

You don't need the counter, PROC TRANSPOSE will add it automatically, but you do need the sort. I think you used to need the counter in previous versions of SAS though.

[vicky07]

You are right. I tried without the counter and it giving same results. Thanks!!

[vicky07]

Thanks!!