transpose - wide to long

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

transpose - wide to long

Hi, This question has been asked and I've referred the solution but my problem is not getting solved and I am a beginner. I have a dataset that looks like below:

HAVE

Variable

Label

V1

V2

V3

A

Afadf

B

Asas

C

Dfd

D

Ysdfdf

E

Adfaf

F

Afadf

G

Adfadf

WANT

ID

Variable

VALUE

V1

A

V1

B

V1

C

V1

D

V1

E

V1

F

V1

G

V2

A

V2

B

V2

C

V2

D

V2

E

V2

F

V2

G

V3

A

V3

B

V3

C

V3

D

V3

E

V3

F

V3

g

The code I used to solve was two steps:

proc transpose data = HAVE out = HAVE2;

     id Variable;

run;

proc transpose data = HAVE2 out = HAVE_FINAL name = variable;

      ID VARIABLE;

     var v1-v3;

run;


WHAT I END UP WITH FROM ABOVE CODE:


A

B

C

D

E

F

G

V1

V2

V3


What am I doing wrong and can the transpose be done in one step ?

thanks


Accepted Solutions
Solution
‎01-21-2015 02:18 PM
Esteemed Advisor
Posts: 7,293

Re: transpose - wide to long

Not strange! Just add notsorted. i.e.:

proc transpose data=have

        out=want (rename=(_name_=id col1=value));

  by variable label notsorted;

  var v1-v3;

run;

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,293

Re: transpose - wide to long

The following would do it. If you really don't want to keep label in the new dataset, just drop it when you specify the data and remove it from the by statement:

proc transpose data=have

        out=want (rename=(_name_=id col1=value));

  by variable label;

  var v1-v3;

run;

Contributor
Posts: 40

Re: transpose - wide to long

It's very strange - i get this error message.


ERROR: Data set WORK.HAVE is not sorted in ascending sequence. The current BY group has

       Variable = avg_weighted_minutes_vie_96 and the next BY group has Variable =

       avg_weighted_minutes_vie_120.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 13 observations read from the data set WORK.HAVE.

WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0

         observations and 0 variables.

WARNING: Data set WORK.WANT was not replaced because this step was stopped.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

Solution
‎01-21-2015 02:18 PM
Esteemed Advisor
Posts: 7,293

Re: transpose - wide to long

Not strange! Just add notsorted. i.e.:

proc transpose data=have

        out=want (rename=(_name_=id col1=value));

  by variable label notsorted;

  var v1-v3;

run;

Contributor
Posts: 40

Re: transpose - wide to long

Hi Arthur, thanks. this works but there is one thing : The structure of output is :

Variable

ID

VALUE

A

V1

A

V2

A

V3

B

V1

B

V2

B

V3

C

V1

C

V2

C

V3

I was aiming for :

ID

Variable

VALUE

V1

A

V1

B

V1

C

V2

A

V2

B

V2

C

V3

A

V3

B

V3

C

Esteemed Advisor
Posts: 7,293

Re: transpose - wide to long

Just add the following:

data want;

  retain id variable value;

  set want;

run;

proc sort data=want;

  by id variable value;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 325 views
  • 6 likes
  • 2 in conversation