Solved
Contributor
Posts: 38

# How to print mean of a variable in the title of the report

I am a beginner in SAS macro and using SAS University Edition. I am trying to write a macro to print a report. This macro will print mean of the variable "Price" in the title of the report. Now there are ways to produce mean in proc report or proc summary but I am not sure how I can calculate mean within the data procedure . I am assuming that I need to calculate the mean in the data procedure and then assign a macro variable so that I can include that in the title of the report.

So my main problem is calculating the mean in a way so that I can put it in the report title. Any help, clue are hugely appreciated.

Here's the sample data-

data exprev;
input Emp_ID \$ 1-10 Sale_Type 14-16 Quantity Price Cost;

datalines;
99999999 1 2 92.60 20.70
99999999 2 14 51.20 12.10
99999999 3 25 31.10 15.65
99999999 2 30 123.70 59.00
99999999 2 8 113.40 28.45
99999999 2 7 41.00 9.25
120458 3 2 146.40 36.70
99999999 2 11 40.20 20.20
99999999 2 100 11.80 5.00
120454 3 20 71.00 32.30
120845 1 19 64.30 28.65
120538 1 22 110.80 29.35
;
run;

And here's my macro code-

%macro newreport(type);
proc print data=work.exprev n='Number of observations for the order type: ';
sum quantity price;
where Sale_Type = &type;
title "Mean price for Sale_Type &type is : ";
run;
%mend;

%newreport(3)

Accepted Solutions
Solution
‎05-19-2017 03:12 PM
Super User
Posts: 13,583

## Re: How to print mean of a variable in the title of the report

Note: Code really should be pasted in the code box opened using the the forum's {i} icon on the menu above. The main message window will reformat pasted text. In this case all of the rows of data as pasted have errors because columns 1-10 as in you input actually read the first two fields into the Emp_id variable.

This will create a macro variable named mean var you could reference in the Title

```proc sql noprint;
select mean(price) into :Meanvar
from exprev;
run;```

However that will likely have more digits than makes sense so you may want

```proc sql noprint;
select put(mean(price),7.2) into :Meanvar
from exprev   where type=&type;
run;```

to round the result to 2 decimals. This step should be in your macro before the print

Your title statement would look like:

title "Mean price for Sale_Type &type is : &meanvar ";

All Replies
Posts: 3,066

## Re: How to print mean of a variable in the title of the report

PROC SQL gives you the mean as a macro variable

```proc sql;
select mean(price) into :meanvalue from exprev;
quit;```

Also, I don't think you want &type to be both in the title and the statement above which checks to see if sale_type is a certain value. Your title needs a different macro variable that has the mean value, in the above example your title would refer to &meanvalue

--
Paige Miller
Contributor
Posts: 38

## Re: How to print mean of a variable in the title of the report

Thanks a lot. This works.

Solution
‎05-19-2017 03:12 PM
Super User
Posts: 13,583

## Re: How to print mean of a variable in the title of the report

Note: Code really should be pasted in the code box opened using the the forum's {i} icon on the menu above. The main message window will reformat pasted text. In this case all of the rows of data as pasted have errors because columns 1-10 as in you input actually read the first two fields into the Emp_id variable.

This will create a macro variable named mean var you could reference in the Title

```proc sql noprint;
select mean(price) into :Meanvar
from exprev;
run;```

However that will likely have more digits than makes sense so you may want

```proc sql noprint;
select put(mean(price),7.2) into :Meanvar
from exprev   where type=&type;
run;```

to round the result to 2 decimals. This step should be in your macro before the print

Your title statement would look like:

title "Mean price for Sale_Type &type is : &meanvar ";

Contributor
Posts: 38

## Re: How to print mean of a variable in the title of the report

Thanks for the note. Will do that next time for sure. Your suggestion was really helpful. Thanks for that too.

Contributor
Posts: 38

## Re: How to print mean of a variable in the title of the report

This is my final code

```%macro report3(type);
proc sql noprint;
select put(mean(price),7.2) into :Meanvar from work.exprev;
where Sale_Type=&type;
run;
proc print data=work.exprev n='Number of observations for the order type: ';
sum price;
where Sale_Type=&type;
Title "Mean price for Sale_Type &type is : &Meanvar ";
run;
%mend;

%report3(2)```

And this is the output. You can see clearly that the mean is supposed to be 381.3/6 = 63.55 instead of 74.79. It seems to me that the PROC SQL statement is ignoring the where clause and calculating mean for the entire data set i.e. all the sales_type instead of only sales_type 2. Can you kindly tell me where I am making mistake ?

Posts: 3,066

## Re: How to print mean of a variable in the title of the report

PROC SQL is ignoring the where clause because you have a semi-colon where it does not belong.

It should be:

```proc sql noprint;
select put(mean(price),7.2) into :Meanvar from work.exprev
where Sale_Type=&type;
run;```
--
Paige Miller
Contributor
Posts: 38