topic Re: transpose data long to wide in New SAS User

transpose data long to wide

vicky07 — Thu, 18 Oct 2018 02:38:31 GMT

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

Re: transpose data long to wide

Reeza — Thu, 18 Oct 2018 02:56:14 GMT

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.

Re: transpose data long to wide

Kurt_Bremser — Thu, 18 Oct 2018 07:54:07 GMT

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;

Re: transpose data long to wide

Reeza — Thu, 18 Oct 2018 14:47:29 GMT

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.

Re: transpose data long to wide

vicky07 — Thu, 18 Oct 2018 18:21:03 GMT

Thanks!!

Re: transpose data long to wide

vicky07 — Thu, 18 Oct 2018 18:23:58 GMT

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