Hey all, I am trying to use PROC REPORT to create the below report that summarizes the number of records by year and highlights when the number falls above/below average or below a certain threshold of 20 records. I have been able to get everything to work with the posted code EXCEPT for a labeled and calculated summary row that shows the average number of records by year and location (last row of pictured 'want' report highlighted in red square). This would be number of records by year, divided by the total number of locations (10). I have tried many different methods but nothing seem to work (I've included 1 of those nonworking methods in the below code - the second compute block). Any ideas of how to add a calculation to a summary row and to label it as average (you can ignore the rest of the formatting unless it pertains to the average row)? Thanks! Want: Currently get (when second compute statement is excluded): proc report data= work.dataset;
columns location=location1 year=year1 year=year2 location=location2 year=year3;
format location location.;
define location1/group ;
define year1/ "Number of Records by Year" group across;
define location2 / "Total" n;
define year2 / group analysis mean noprint ;
define year3 / group analysis sum noprint "Average";
rbreak after / summarize dol dul;
compute year2;
*2019;
if (_C2_) in (21:1381) then do ;
call define('_c2_','style','style=[background=lightyellow]');
end;
if (_C2_) >= 1382 then do;
call define('_c2_','style','style=[background=lightgreen]');
end;
if (_C2_) <= 20 then do;
call define('_c2_','style','style=[background=lightred]');
end;
*2020;
if (_C3_) in (21:655) then do ;
call define('_C3_','style','style=[background=lightyellow]');
end;
if (_C3_) >= 656 then do;
call define('_C3_','style','style=[background=lightgreen]');
end;
if (_C3_) <= 20 then do;
call define('_C3_','style','style=[background=lightred]');
end;
*2021;
if (_C4_) in (21:945) then do ;
call define('_C4_','style','style=[background=lightyellow]');
end;
if (_C4_) >= 946 then do;
call define('_C4_','style','style=[background=lightgreen]');
end;
if (_C4_) <= 20 then do;
call define('_C4_','style','style=[background=lightred]');
end;
endcomp;
compute before year3 ;
year3= year3/10 "Average";
line ' ';
endcomp;
rbreak after / summarize style=Header;
options missing=0;
run;
... View more