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
Can you provide sample output...
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
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))
...;
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
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?
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?
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
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... )-:
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 ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.