topic Re: Retain variables when reshaping long to wide in SAS Procedures

Retain variables when reshaping long to wide

JBCooney — Fri, 15 May 2015 23:12:49 GMT

I am attempting to reshape the following long data set to a wide data set and retain individual identifying information. The data set has missing values. I can transpose the repeating VAR values but can't seem to carry the TRT and SEX values with the transposition.

Long Data Set

ID TIME VAR TRT SEX

01 1 1150 1 M

01 2 1158 1 M

02 1 1201 1 F

02 2 1236 1 F

02 3 1240 1 F

03 2 1358 2 M

04 1 1285 2 F

04 2 1280 2 F

04 3 1290 2 F

Desired Wide Data Set

ID VAR1 VAR1 VAR3 TRT SEX

01 1150 1158 . 1 M

02 1201 1236 1240 1 F

03 . 1358 . 2 M

04 1285 1280 1290 2 F

Scoured previous posts and could not find a similar example. Thanks for any assistance.

Re: Retain variables when reshaping long to wide

PGStats — Sat, 16 May 2015 18:05:03 GMT

Try

proc transpose data=long out=wide prefix=var;

by id trt sex notsorted;

id time;

var var;

run;

Re: Retain variables when reshaping long to wide

JBCooney — Sun, 17 May 2015 00:36:54 GMT

Dear PGStats,

I forgot to specify sex $ when I ran the example in SAS UE. Nevertheless, your suggestion scaled up nicely to my larger data set. The NOTSORTED option in the BY statement seems to be what I was missing.

Many thanks!!!

Re: Retain variables when reshaping long to wide

Jagadishkatam — Sun, 17 May 2015 01:35:09 GMT

Alternatively by arrays in datastep

Thanks,

Jag

Re: Retain variables when reshaping long to wide

JBCooney — Mon, 25 May 2015 01:15:56 GMT

Jag,

Thanks so much for your guidance. After some study your reply has really helped me to better understand array processing in SAS. Very powerful.

Best,

John