BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mistervv
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Chrishi
Calcite | Level 5


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

Ksharp
Super User

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

Mistervv
Calcite | Level 5

Thanks for the help everyone!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 984 views
  • 6 likes
  • 4 in conversation