transpose data

Reply
Contributor
Posts: 64

transpose data

Hi Experts,

I need to transpose the following data by Var3 so that all the attribute of VAR3 could become separate variable. Please help me to do so.

VAR1  VAR2  VAR3  VAR4  VAR5  VAR6  VAR7  VAR8

A        P         X        M       12       54      12        26

B        Q        Y        N        43       43      32        75

C        R         Z        O         4       45      15        17

D        S         A        P       98       34      65        66

Regards

Rahul

Contributor
Posts: 65

Re: transpose data

Posted in reply to Rahul_SAS

Can you provide sample output...

Contributor
Posts: 64

Re: transpose data

Posted in reply to MadhuKorni

the output shoul look like this:

VAR1  VAR2  VAR3  VAR4  VAR5  VAR6  VAR7  VAR8      X     Y     Z     A

A        P         X        M       12       54      12        26          5     4     6     7

B        Q        Y        N        43       43      32        75          6     5     4     8

C        R         Z        O         4       45      15        17          3     3      7     4

D        S         A        P       98       34      65        66          23    1     6     3

Super User
Super User
Posts: 7,942

Re: transpose data

Posted in reply to Rahul_SAS

Sorry, your data doesn't seem to make sense.  If X, Y, Z, A are from VAR3, where does the data 5,4,6,7 come from?   Also, what are you trying to achieve here.  It looks to me like the data itself is a mess and you would be better served by sorting that out first.  Get it into a normalised structure:

TYPE               RESULT

A                       12

B                        43

...

Easy enough to do,

data want;

     set have (keep=var1 var4 rename=(var1=var var4=result))

            have (keep=var2 var5 rename=(var2=var var5=result))

...;

Contributor
Posts: 64

Re: transpose data

lets make it simple-

HAVE:

VAR1  VAR2  VAR3  VAR4  Sale

A        P         X        M       12      

B        Q        Y        N        43      

C        R         Z        O         4      

D        S         A        P       98      

WANT:

VAR1  VAR2  VAR3  VAR4  Sale    X     Y     Z     A

A        P         X        M       12       3     2     6     1

B        Q        Y        N        43       8    10    9     16

C        R         Z        O         4       2     1     0     1

D        S         A        P       98       23    25   30   20

Contributor
Posts: 65

Re: transpose data

Posted in reply to Rahul_SAS

It is clear that the sum of X,Y,Z,A is the sale value.

But how can we get the values of X,Y,Z,A?

Super User
Super User
Posts: 7,942

Re: transpose data

Posted in reply to Rahul_SAS

Sorry, that's not simple.  There is no explanation of what then numbers in X,Y,Z,A are or how they are calculated.  Why is Y 2 for the first row and 10 for the second row?

Contributor
Posts: 64

Re: transpose data

an small correction in the output dataset....

VAR1  VAR2  VAR3  VAR4  Sale    X     Y     Z     A

A        P         X        M       12       3     22    1     21

B        Q        Y        N        43       2    10     3     36

C        R         Z        O         4       6     5      0     40

D        S         A        P       98       1     6      3     1

Contributor
Posts: 64

Re: transpose data

Posted in reply to Rahul_SAS

so, suppose if we have a dataset with 5 dimensions and 4 measures and want to convert 1 dimension out of 5 to separate variables. how can we do the same.

Cann't we achieve the same using Proc Transpose???

I am trying it but not able to do so... )-:

Super User
Posts: 10,020

Re: transpose data

Posted in reply to Rahul_SAS

You didn't answer RW's question. How do we get X Y Z A, What is your logic ?

I know sum(X)=X sum(Y)=Y but sum(Z) ^=Z ,Why ?

Ask a Question
Discussion stats
  • 9 replies
  • 375 views
  • 0 likes
  • 4 in conversation