topic Re: How do I keep adding macro variables in dataset? in SAS Programming

How do I keep adding macro variables in dataset?

sasworker16 — Fri, 20 Jul 2018 05:47:06 GMT

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;

Re: keep macro variable in dataset

Kurt_Bremser — Fri, 20 Jul 2018 05:48:04 GMT

Transpose your dataset and use proc means/summary.

You only need to add new variables in the var statement. No need for a macro.

Re: How do I keep adding macro variables in dataset?

Kurt_Bremser — Fri, 20 Jul 2018 06:33:52 GMT

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.

Re: How do I keep adding macro variables in dataset?

Tom — Fri, 20 Jul 2018 12:44:55 GMT

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;