topic Re: Transpose multiple variables in SAS Procedures

Transpose multiple variables

Mistervv — Fri, 04 Jul 2014 08:15:56 GMT

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

Re: Transpose multiple variables

RW9 — Fri, 04 Jul 2014 08:42:00 GMT

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;

Re: Transpose multiple variables

Chrishi — Fri, 04 Jul 2014 10:22:50 GMT

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

Re: Transpose multiple variables

Ksharp — Fri, 04 Jul 2014 13:15:50 GMT

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

Re: Transpose multiple variables

RW9 — Fri, 04 Jul 2014 13:18:33 GMT

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

Re: Transpose multiple variables

Mistervv — Mon, 07 Jul 2014 15:26:39 GMT

Thanks for the help everyone!