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

[shihabur]

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)

[PaigeMiller]

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

[shihabur]

Thanks a lot. This works. 

[ballardw · Accepted solution]

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 ";

[shihabur]

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

[shihabur]

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 ?

[PaigeMiller]

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;

[shihabur]

Thanks a lot.

[mjabed600]

Hey, I am trying to do something similar i.e. create a report with the mean of a variable (price) in the title but I wanted to know how would you create a macro variable for the mean to reference in the title without the proc sql step. would it be easier to do with proc means or proc report?