BookmarkSubscribeRSS Feed
Rahul_SAS
Quartz | Level 8

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

9 REPLIES 9
MadhuKorni
Quartz | Level 8

Can you provide sample output...

Rahul_SAS
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

...;

Rahul_SAS
Quartz | Level 8

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

MadhuKorni
Quartz | Level 8

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Rahul_SAS
Quartz | Level 8

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

Rahul_SAS
Quartz | Level 8

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

Ksharp
Super User

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 ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 910 views
  • 0 likes
  • 4 in conversation