Compute and Label a summary row in Proc Report

[bananah13]

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;

[Cynthia_sas]

Hi: Can you post your data in the form of a DATA step program that creates your WORK.DATASET? Otherwise, no one can run your code without making the data for testing.
Cynthia

[bananah13]

Ok, I can't edit my post but have attached here a dummy data set and what the actual outputs look like with the above posted code on the dummy2 data:

What I want:

What I get:

[Ksharp]

Why not try my code firstly ?

libname x v9 'c:\temp';

data have;
 set x.dummy2;
run;
proc sql;
create table report as
select repeat(' ',20-location)||put(location,best. -l) as location length=40,put(year,best. -l) length=40 as _year,count(*) as count from have group by location,year
union all
select repeat(' ',20-location)||put(location,best. -l),'Total',count(*) as count from have group by location
union all
select 'Average',put(year,best. -l),count(*)/(select count(distinct location) from have) from have group by year
union all
select 'Average','Total',(select count(*) from have)/(select count(distinct location) from have) from have(obs=1)
;
quit;

proc report data=report nowd;
column location count,_year;
define location/group 'Location';
define _year/across 'Year';
define count/analysis sum '' f=20.0;
run;

[ballardw]

---

@bananah13 wrote:

Ok, I can't edit my post but have attached here a dummy data set and what the actual outputs look like with the above posted code on the dummy2 data:

---

If you click on the three lines next to your post's subject line you should see an option to edit the post.

[Ksharp]

/*
That is clumsy for PROC REPORT to calculate these statistics,
But easy for PROC SQL.
*/
data have;
 set sashelp.stocks;
 year=year(date);
run;
proc sql;
create table report as
select ' '||stock as stock length=40,put(year,best. -l) length=40 as _year,count(*) as count from have group by stock,year
union all
select ' '||stock,'Total',count(*) as count from have group by stock
union all
select 'Average',put(year,best. -l),count(*)/(select count(distinct stock) from have) from have group by year
union all
select 'Average','Total',(select count(*) from have)/(select count(distinct stock) from have) from have(obs=1)
;
quit;

proc report data=report nowd;
column stock count,_year;
define stock/group 'Location';
define _year/across 'Year';
define count/analysis sum '';
run;

[bananah13]

This doesn't seem to compute each row separately or give me the average summary row. 

[ballardw · Accepted solution]

---

@bananah13 wrote:

This doesn't seem to compute each row separately or give me the average summary row. 

---

You now have a bit of obligation to provide actual data in the form of a working data step and show the actual code you used that "does seem to compute each row separately". It may also mean a bit of a description of how you think the "average summary row" is supposed to be calculated.

Claiming something does not work without working examples is poor practice when asking for help.

[Cynthia_sas]

Hi:

  In the interest of providing a PROC REPORT solution, here's what I was able to do with your dummy2 data:

I believe that #3 is the one you want. The reason that the word "Average" was not appearing was that your Location variable is a numeric variable, so in my solution, I needed to make a character version of Location and I called it Char_Loc.  Then, at the break in a COMPUTE AFTER block, I could assign the string Average to the Char_loc column. You'll see that in Example #1, before I did the divide I just generated a simple summary and put the string "Grand Total" on the break line. In Example #2, in the COMPUTE AFTER block, I divided the summary values by a hard-coded constant of 10.

  Example #3 isn't entirely all PROC REPORT. I did use a PROC SQL step to make the &TOTLOC macro variable that held the count of distinct values for Location, so I could use &TOTLOC in the division instead of a hard-coded value.

Cynthia

Here's the code I used with a copy of DUMMY2 in the WORK library:

** Example 1 Get Grand Total on Break line;
** need character version of Location to hold string; 
 
proc report data=dummy2;
  title '1) Make sure Summary Line is correct';
  column location char_loc  year,(n=yr_tot ) n=Gtot ;
  define location / group  'Numeric Loc';
  define char_loc / computed 'Char Loc '
      style(column)={font_weight=bold};
  define year / across;
  define yr_tot /  "Sum" ;
  define Gtot / 'Total';
  compute char_loc / character length=20;
    char_loc = put(location,words.);
  endcomp;
  rbreak after / summarize;
  compute after;
	Char_Loc = 'Grand Total';
  endcomp;
run;

** Example 2 Divide by constant value (10) so the values on the break are the average for 10 locations';
proc report data=dummy2;
  title '2) Change the style and divide sums on break line by a fixed value';
  column location char_loc  year,(n=yr_tot ) n=Gtot ;
  define location / group /* noprint */;
  define char_loc / computed ' '
      style(column)={font_weight=bold};
  define year / across;
  define yr_tot /  "Sum" ;
  define Gtot / 'Total';
  compute char_loc / character length=20;
    char_loc = put(location,words.);
  endcomp;
  rbreak after / summarize;
  compute after;
  ** assuming all you want to do is divide all of the sums by 10;
  ** use a hard-coded number in the formula;
    _c3_ = _c3_ / 10;
	_c4_ = _c4_ / 10;
	_c5_ = _c5_ / 10;
	Gtot = Gtot / 10;
	Char_Loc = 'Average';
  endcomp;
run;

** Ex 3 Use PROC SQL to get the count of location values;
proc sql noprint;
  select count(distinct location) into :totloc
  from work.dummy2;
quit;
%put The number of unique location values is &=totloc;

proc report data=dummy2;
  title '3) Use a macro variable for the division on the break line';
  column location ('Location' char_loc) year,(n=yr_tot ) ('Total' n=Gtot);
  define location / group  noprint ;
  define char_loc / computed ' '
      style(column)={font_weight=bold};
  define year / across;
  define yr_tot /  " " ;
  define Gtot / ' ';
  compute char_loc / character length=20;
    char_loc = put(location,words.);
  endcomp;
  rbreak after / summarize;
  compute after;
  ** Now use the macro variable in the division;
    _c3_ = _c3_ / &totloc;
	_c4_ = _c4_ / &totloc;
	_c5_ = _c5_ / &totloc;
	Gtot = Gtot / &totloc;
	Char_Loc = 'Average';
  endcomp;
run;