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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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: 11,134

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,795

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

 

Contributor
Posts: 29

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: 11,134

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: 29

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: 29

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 ?Screen Shot 2017-05-19 at 2.51.40 PM.png

Trusted Advisor
Posts: 1,795

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;
Contributor
Posts: 29

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

Thanks a lot.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 179 views
  • 2 likes
  • 3 in conversation