## Transpose multiple variables

Solved
Occasional Contributor
Posts: 5

# Transpose multiple variables

Hello,

I’m a first time SAS user and I would like to get some advice on a data management issue. I have searched the forums and the internet but I find it rather hard to understand it all as I haven"t had training in SAS yet. I would like to put some data in a new perspective.

The file I have now is like this (simplistic example):

Center                  Blood Level male              Blood level female                                         BMI male            BMI female

1                            50                                                        60                                         20                          18

2                            60                                                        70                                         25                          23

3                            70                                                        80                                         22                          21

And it should be something like this:

center                  male                     female

Blood Level              1                            50                          60

Blood Level              2                            60                          70

Blood level               3                            70                          80

BMI                        1                            20                          18

BMI                       2                            25                          23

BMI                       3                            22                          21

So I have to transpose multiple variables and I also have to make new “general” observations (for instance here: blood level and BMI).

Thanks to anyone who’s willing to help me out here!

Greetings,

Rob

Accepted Solutions
Solution
‎07-04-2014 04:42 AM
Super User
Posts: 9,599

## Re: Transpose multiple variables

That's not transposing as such, but normalizing.  To transpose you take vertical data and make it horizontal, and for normalizing its the reverse.  Something like:

data want (keep=section center male female);

set have;

attrib section format=\$200. male female format=best.;

section="Blood Level";

male=blood_level_male;

female=blood_level_female;

output;

section="BMI";

male=bmi_male;

female=bmi_female;

output;

run;

All Replies
Solution
‎07-04-2014 04:42 AM
Super User
Posts: 9,599

## Re: Transpose multiple variables

That's not transposing as such, but normalizing.  To transpose you take vertical data and make it horizontal, and for normalizing its the reverse.  Something like:

data want (keep=section center male female);

set have;

attrib section format=\$200. male female format=best.;

section="Blood Level";

male=blood_level_male;

female=blood_level_female;

output;

section="BMI";

male=bmi_male;

female=bmi_female;

output;

run;

Super User
Posts: 9,599

## Re: Transpose multiple variables

To note, there is a very similar thread here: https://communities.sas.com/message/218426#218426

Contributor
Posts: 29

## Re: Transpose multiple variables

By only transposing you might not get the desired result. Try the below code. hope it will be useful.

data test;

input Center Blood_M Blood_F BMI_M BMI_F;

cards;

1 50 60 20 18

2 60 70 25 23

3 70 80 22 21

;

proc transpose data=test out=test1;

var Blood_M BMI_M Blood_F BMI_F ;

by center;

run;

data result;

merge test1(where=(_name_ in ('Blood_M' 'BMI_M')) rename=(col1=col2))

test1(where=(name in ('Blood_F' 'BMI_F')) rename=(_name_=name));

run;

data result1;

set result;

rename col2=Male col1=Female ;

stat=substr(name,1,length(name)-2);

drop _name_ name;

run;

Sudeer

Super User
Posts: 10,787

## Re: Transpose multiple variables

Or if you have lots and lots of variables and don't want type it one by one . -- double proc transpose.

```data test;
input Center Blood_M Blood_F BMI_M BMI_F;
cards;
1 50 60 20 18
2 60 70 25 23
3 70 80 22 21
;
proc transpose data=test out=test1(where=(_name_ ne 'Center'));
by center;
var _numeric_ ;
run;
data test1;
set test1;
field=     scan(_name_,1,'_') ;
name=scan(_name_,-1,'_');
run;
proc sort data=test1;by center field;run;
proc transpose data=test1 out=want(drop=_:) ;
by center field;
var col1;
id name;
run;
```

Xia Keshan

Occasional Contributor
Posts: 5

## Re: Transpose multiple variables

Thanks for the help everyone!

🔒 This topic is solved and locked.