BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shihabur
Obsidian | Level 7

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)

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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
shihabur
Obsidian | Level 7

Thanks a lot. This works. 

ballardw
Super User

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
Obsidian | Level 7

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

shihabur
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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
mjabed600
Fluorite | Level 6

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 8 replies
  • 1628 views
  • 2 likes
  • 4 in conversation