Help using Base SAS procedures

Transpose multiple variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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
Super User
Posts: 7,401

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;

View solution in original post


All Replies
Solution
‎07-04-2014 04:42 AM
Super User
Super User
Posts: 7,401

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
Super User
Posts: 7,401

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: 9,681

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 281 views
  • 6 likes
  • 4 in conversation