topic Re: percentage calculation in proc report in ODS and Base Reporting

percentage calculation in proc report

prajakta — Fri, 27 Dec 2019 12:40:45 GMT

I'm trying to generate report that look like

test --> TAT REPORT 24Dec2019
TAT	less than 5 hours	5 - 8 hours	More than 8 hours	Grand Total
FTR	0	0	0	0
%	100%	0%	0%
FTNR	1	1	108	110
%	1%	1%	98%
Grand Total	17	1	108	126
%	13%	1%	86%

Below is the code i'm using

PROC REPORT DATA=test_b
style(header)= {FOREGROUND = WHITE BACKGROUND = MAROON FONT = ('Calibri',7.5PT) FONTWEIGHT = BOLD BORDERCOLOR =BLACK}
style(column)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center}
style(report)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center} ;
column ("test --> TAT REPORT &REPORT_DT" tat 'less than 5 hours'n '5-8 hours'n 'More than 8 hours'n 'Grand Total'n);
RUN;

Output:

test --> TAT REPORT 27DEC2019
TAT	less than 5 hours	5-8 hours	More than 8 hours	Grand Total
FTNR	1	1	107	109
%	0.0091743	0.0091743	0.9816514	.
FTR	16	.	1	17
%	0.9411765	.	0.0588235	.

I'm unable to figure out is how to calculate the "Grand Total" and "Total percentage".

Re: percentage calculation in proc report

PaigeMiller — Fri, 27 Dec 2019 13:28:46 GMT

You might be better off using PROC FREQ to compute the percents and counts, and this will produce a table very very close to what you show. If that table isn't exactly what you want, you could manipulate the output of PROC FREQ in a DATA step to be exactly what you want, and then use PROC REPORT.

Maybe there's a way to get these rows of percents using only PROC REPORT, but I'm sure the above is easier.

Re: percentage calculation in proc report

ed_sas_member — Fri, 27 Dec 2019 13:43:21 GMT

Hi @prajakta

Could you please provide some sample data?

Re: percentage calculation in proc report

ed_sas_member — Fri, 27 Dec 2019 16:45:07 GMT

Hi @prajakta

Here is a quite more complex code to achieve the desired output using a proc report.

Please note that the input data is as follows -> to be adapted in case it is not exactly yours (maybe run a proc freq before)

data test_b;
	length TAT $ 100;
	input TAT $ 1-4 Duration 6 count 8-10;
	datalines;
FTR  1 16
FTR  2 0
FTR  3 0
FTNR 1 1
FTNR 2 1
FTNR 3 108
;
run;

proc format;
	value Duration 
		1 = "Less than 5 hours"
		2 = "5 - 8 hours"
		3 = "More than 8 hours";
run;

proc report data=test_b
		
	style(header)={FOREGROUND=WHITE BACKGROUND=MAROON 
				   FONT=('Calibri', 7.5PT) FONTWEIGHT=BOLD BORDERCOLOR=BLACK} 
	style(column)={FONT=('Calibri', 7.5PT) BORDERCOLOR=BLACK just=center} 
	style(report)={FONT=('Calibri', 7.5PT) BORDERCOLOR=BLACK just=center};

	column ("test --> TAT  REPORT &REPORT_DT" TAT Duration, (count pct _dummy) ntot);
	
	define TAT		/ group;
	define Duration	/ across order=data format=Duration.;
	define count	/ analysis noprint;
	define pct		/ computed f=percent8.0 '%' noprint;
	define _dummy	/ computed '';
	define ntot		/ computed 'Grand Total';
	
	rbreak after	/summarize;
	break after TAT / summarize ;
	
	compute before TAT;
		den=_C2_ + _C5_ + _C8_;
	endcomp;
	
	compute after TAT;
	 	TAT = "%";
	 	_c3_ = _c2_ / den;	
		_c6_ = _c5_ / den;
		_c9_ = _c8_ / den;
	endcomp;

	compute _dummy / char;
		if _BREAK_ = "_RBREAK_" or (_C1_ in ("FTR" "FTNR") and _BREAK_ ="") then do;
			_C4_ = _C2_;
			_C7_ = _C5_;
			_C10_= _C8_;
		end;	
		else do;
			_C4_ = put(_c2_ / den, percent8.0);
			_C7_ = put(_c5_ / den, percent8.0);
			_C10_= put(_c8_ / den, percent8.0);
		end;

	endcomp;
	
	compute ntot;
		if _BREAK_ = "_RBREAK_" or (_C1_ in ("FTR" "FTNR") and _BREAK_ ="") then ntot=_C2_ + _C5_ + _C8_;
	endcomp;

	compute after;
	   	TAT = "Grand Total";
	   	_c3_ = _c2_ / 126;	
		_c6_ = _c5_ / 126;
		_c9_ = _c8_ / 126;
	endcomp;
run;

Output;

Re: percentage calculation in proc report

ballardw — Fri, 27 Dec 2019 18:02:18 GMT

@prajakta wrote:

I'm trying to generate report that look like

test --> TAT REPORT 24Dec2019

TAT less than 5 hours 5 - 8 hours More than 8 hours Grand Total

FTR 0 0 0 0

% 100% 0% 0%

FTNR 1 1 108 110

% 1% 1% 98%

Grand Total 17 1 108 126

% 13% 1% 86%

Below is the code i'm using

PROC REPORT DATA=test_b
style(header)= {FOREGROUND = WHITE BACKGROUND = MAROON FONT = ('Calibri',7.5PT) FONTWEIGHT = BOLD BORDERCOLOR =BLACK}
style(column)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center}
style(report)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center} ;
column ("test --> TAT REPORT &REPORT_DT" tat 'less than 5 hours'n '5-8 hours'n 'More than 8 hours'n 'Grand Total'n);
RUN;

Output:

test --> TAT REPORT 27DEC2019

TAT less than 5 hours 5-8 hours More than 8 hours Grand Total

FTNR 1 1 107 109

% 0.0091743 0.0091743 0.9816514 .

FTR 16 . 1 17

% 0.9411765 . 0.0588235 .

I'm unable to figure out is how to calculate the "Grand Total" and "Total percentage".

You will find working with SAS that often creating variables such as you "less than 5" and such is not flexible and can make certain types of calculations harder than necessary. You can create groups of values with a FORMAT to display desired text for the actual underlying values. And then using the format with the simpler data of one record per value generating the report relatively easy.

The example below creates a custom format to create 3 groups from a range of values. Then an example data set that has two values for a TAT variable and randomly creates a number Hour values. Then uses Proc Tabulate (Proc Report does really like to display statistics the way you have shown your desired value) to group the hour values, count and determine the row percentage for each group, a grand total column to show the counts and percent (which is going to be 100) as well as summary row to show the hour group totals.

proc format library=work;
value demo
1 -<5 = 'less than 5 hours'
5 - 8 = '5-8 hours'
8<-high='More than 8 hours'
;

data example;
   tat = 'FTNR';
   Do i= 1 to 47;
      hour = rand('integer',1,15);
      output;
   end;
   tat = 'FTR';
   Do i= 1 to 59;
      hour = rand('integer',1,17);
      output;
   end;
run;

proc tabulate data=example;
   class tat'
   class hour/ order=internal;
   format hour demo.;
   table (tat All='Total')*(n rowpctn='%'*f=8.4),
         hour=' '  All='Grand Total'
    
   ;
run;

Tabulate will allow the style overrides but is done differently than in Proc report depending on what you want. CLASSLEV statement sets style elements for the row/ column header cells of CLASS variables for example.

One of the very strong advantages of this approach is if someone asks to show 4 groups at different intervals, or 2 groups. Then the only changes needed are to create an additional format and use that format in the Procedure code. Your approach would require going back through the data and creating additional variables.

The groups created by formats in this way are honored by almost all of the analysis or graphing procedures. So again code that works with one format only needs to have the format changed to create different analysis or graphs. Sort behavior is also easier to manage with numeric values underlying the format.

Re: percentage calculation in proc report

PaigeMiller — Fri, 27 Dec 2019 20:09:34 GMT

What a shame to use PROC REPORT or PROC TABULATE here when PROC FREQ gives the exact same table with much less work.