Hi, I've asked a similar question before but this one is slightly different...
I am using Proc Report to generate a report... the only issue is that when I am displaying "target" values in the rows I want to show it as an average but in the total I want to show a sum of all the average rows... It's confusing to explain that's why I've attached a snapshot
In the attached snapshot, total target for North America should be 115 and that of South America should be 200
Here is the code that I've used:
data someData2;
infile cards dsd dlm="," missover;
input
countryGroup : $32.
Country : $32.
target : 8.
Day : 8.
volume : 8.
;
cards;
NorthAmerica,Canada,100,1,10
NorthAmerica,Canada,100,2,120
NorthAmerica,Canada,100,3,75
NorthAmerica,USA,100,1,110
NorthAmerica,USA,10,2,20
NorthAmerica,USA,5,3,25
NorthAmerica,USA,5,4,15
NorthAmerica,USA,5,5,5
NorthAmerica,USA,5,6,5
NorthAmerica,USA,5,7,1
NorthAmerica,USA,5,8,7
NorthAmerica,USA,5,9,25
NorthAmerica,USA,5,10,65
SouthAmerica,Brazil,75,1,10
SouthAmerica,Brazil,75,2,50
SouthAmerica,Paraguay,100,1,10
SouthAmerica,Paraguay,50,2,10
SouthAmerica,Peru,50,1,17
SouthAmerica,Peru,50,2,10
SouthAmerica,Peru,50,3,100
;
run;
options missing=0;
ods html file='c:\absolute_cols_1.html' style=htmlblue;
proc report data= someData2 nowd
style(column)={font_face=times font_size=8pt}
style(header)={just=center font_face=arial};
title 'Report';
column CountryGroup Country target (volume,day);
define countryGroup / group 'Group '
style(column)=[background=gray foreground=white
font_weight=bold];
define country / order=formatted group ' '
style(column)=[background=gray foreground=white
font_weight=bold];
define target / analysis mean;
define day / across 'Day ' ORDER=INTERNAL;
define volume / analysis sum format=comma10. ' ';
compute volume;
** remove all possibility that the issue is with the ARRAY;
** statement or the VNAME function;
if missing(_break_) then do;
if _c4_ > target.mean then
call define('_c4_', "style", "style={background=cx997086}");
if _c5_ > target.mean then
call define('_c5_', "style", "style={background=cx997086}");
if _c6_ > target.mean then
call define('_c6_', "style", "style={background=cx997086}");
if _c7_ > target.mean then
call define('_c7_', "style", "style={background=cx997086}");
if _c8_ > target.mean then
call define('_c8_', "style", "style={background=cx997086}");
if _c9_ > target.mean then
call define('_c9_', "style", "style={background=cx997086}");
if _c10_ > target.mean then
call define('_c10_', "style", "style={background=cx997086}");
if _c11_ > target.mean then
call define('_c11_', "style", "style={background=cx997086}");
if _c12_ > target.mean then
call define('_c12_', "style", "style={background=cx997086}");
if _c13_ > target.mean then
call define('_c13_', "style", "style={background=cx997086}");
end;
endcomp;
compute after CountryGroup;
CountryGroup = catx(" ", CountryGroup, "Total");
endcomp;
compute after;
CountryGroup = "Grand Total" ;
endcomp;
break after CountryGroup/summarize;
rbreak after / summarize;
run;
ods _all_ close;
Please advice
Thanks
Hi:
You are asking for the MEAN statistic for your variable TARGET. That is the only statistic that PROC REPORT can give you at the break point on the report. If you want some other value, you will either have to
1) calculate it yourself and change the value within PROC REPORT
2) calculate it yourself outside of PROC REPORT and use a different variable for the summary inside of PROC REPORT
3) change the data before PROC REPORT, probably using FIRST. and/or LAST. logic to keep the first value for a group and then change to the SUM statistic. For example, if you changed your input data, temporarily, like this:
NorthAmerica,Canada,100,1,10
NorthAmerica,Canada,0,2,120
NorthAmerica,Canada,0,3,75
NorthAmerica,USA,115,1,110
NorthAmerica,USA,0,2,20
NorthAmerica,USA,0,3,25
NorthAmerica,USA,0,4,15
NorthAmerica,USA,0,5,5
NorthAmerica,USA,0,6,5
NorthAmerica,USA,0,7,1
NorthAmerica,USA,0,8,7
NorthAmerica,USA,0,9,25
NorthAmerica,USA,0,10,65
Then the SUM statistic would work as you want when you do GROUPING and on the summary line for TARGET. If you remember, back in the first examples of this problem, you had two solutions -- one that used the MEAN (because your TARGET value was on every line) and one that used changed input data numbers and the SUM statistic. This is why you had those two different solutions -- due to the structure of your INPUT data and the way you represented TARGET in the input data. If you are getting a summary report (one row for every countryGroup/Country combination, then you do not need the TARGET number on every row. I don't quite understand the logic of why the values for NorthAmerica/USA changed from 100 to 10 to 5, while the values for CANADA stayed constant at 100 on each row. So there's probably more to understand about your data, but the bottom line is that PROC REPORT can only give you the statistic in the DEFINE statement automatically at a break. You can put another value in the cell using a COMPUTE block, but you have to know what the correct value is.
cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.