BookmarkSubscribeRSS Feed
sasworker16
Calcite | Level 5

In this dataset, I have body mass index and total energy intake measurements for college students.

 

I want to calculate the mean body mass index and energy intake for each student using macro in SAS.

 

Using the code below only replaces the mean values with the last variable.

 

Is there a way to keep adding the variables in macro? 

 

Ideally, I want the final dataset to have mean body mass index as well as mean energy intake for each student.

 

This will be useful when I collect more data such as blood pressure...

 

Thanks.


data student;
input person_id bmi_1st energy_1st bmi_2nd energy_2nd ;
datalines;
1001 25 2000 23 1900
1002 21 1992 25 2190
1003 23 2123 25 3215
1004 29 2322 28 1555
1005 25 2222 23 1985
;
run;

%macro data(var);
data student_stat; set student;
&var._mean=(&var._1st+&var._2nd)/2;
run;

%mend (data);
%data(bmi)
%data(energy)
run;

 

3 REPLIES 3
Kurt_Bremser
Super User

Just to expand on my previous post:

Transpose to a helpful long dataset format:

data student;
input person_id bmi_1st energy_1st bmi_2nd energy_2nd ;
datalines;
1001 25 2000 23 1900
1002 21 1992 25 2190
1003 23 2123 25 3215
1004 29 2322 28 1555
1005 25 2222 23 1985
;
run;

proc transpose data=student out=int1;
by person_id;
var bmi: energy:;
run;

data int2;
set int1;
varname = scan(_name_,1,'_');
event = input(substr(scan(_name_,2,'_'),1,anyalpha(scan(_name_,2,'_'))-1),best.);
drop _name_;
run;

proc sort data=int2;
by person_id event;
run;

proc transpose data=int2 out=have (drop=_name_);
by person_id event;
var col1;
id varname;
run;

proc print data=have noobs;
run;

You now have a nice longitudinal dataset:

person_
   id      event    bmi    energy

  1001       1       25     2000 
  1001       2       23     1900 
  1002       1       21     1992 
  1002       2       25     2190 
  1003       1       23     2123 
  1003       2       25     3215 
  1004       1       29     2322 
  1004       2       28     1555 
  1005       1       25     2222 
  1005       2       23     1985 

that lends itself nicely to analysis:

proc summary data=have mean;
by person_id;
var bmi energy;
output out=want mean()=;
run;

proc print data=want noobs;
run;

Result:

person_
   id      _TYPE_    _FREQ_     bmi    energy

  1001        0         2      24.0    1950.0
  1002        0         2      23.0    2091.0
  1003        0         2      24.0    2669.0
  1004        0         2      28.5    1938.5
  1005        0         2      24.0    2103.5

Bottom line: don't store data (event sequence) in structure (variable names). See Maxims 19 and 33.

 

Tom
Super User Tom
Super User

The problem is you are running multiple data steps and each one is starting from the same input data.  So the last one overwrites the previous one.  Make your macro only generate statements instead of steps. THen call it inside of data step to have it generate those statements all on one data step.

%macro data(var);
&var._mean=mean(of &var._:);
%mend data;

data student_stat;
  set student;
  %data(bmi)
  %data(energy)
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 857 views
  • 0 likes
  • 3 in conversation