topic Re: transpose data in SAS Programming

transpose data

Rahul_SAS — Thu, 09 Jul 2015 07:29:35 GMT

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

Re: transpose data

MadhuKorni — Thu, 09 Jul 2015 07:41:50 GMT

Can you provide sample output...

Re: transpose data

Rahul_SAS — Thu, 09 Jul 2015 08:35:03 GMT

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

Re: transpose data

RW9 — Thu, 09 Jul 2015 08:42:46 GMT

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

...;

Re: transpose data

Rahul_SAS — Thu, 09 Jul 2015 08:57:03 GMT

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

Re: transpose data

MadhuKorni — Thu, 09 Jul 2015 09:06:48 GMT

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?

Re: transpose data

RW9 — Thu, 09 Jul 2015 09:15:15 GMT

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?

Re: transpose data

Rahul_SAS — Thu, 09 Jul 2015 12:25:40 GMT

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

Re: transpose data

Rahul_SAS — Fri, 10 Jul 2015 06:31:56 GMT

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

Re: transpose data

Ksharp — Fri, 10 Jul 2015 12:25:06 GMT

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 ?