## transpose data

Frequent Contributor
Posts: 79

# 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

Frequent Contributor
Posts: 77

## Re: transpose data

Can you provide sample output...

Frequent Contributor
Posts: 79

## Re: transpose data

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
Posts: 9,599

## Re: transpose data

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))

...;

Frequent Contributor
Posts: 79

## 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

Frequent Contributor
Posts: 77

## Re: transpose data

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
Posts: 9,599

## Re: transpose data

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?

Frequent Contributor
Posts: 79

## 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

Frequent Contributor
Posts: 79

## Re: transpose data

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,778