topic Re: Display percentage of Grand total per column in Proc Report in SAS Programming

Display percentage of Grand total per column in Proc Report

Q1983 — Wed, 26 May 2021 16:08:27 GMT

data shoes;
set sashelp.shoes;
if region in ('Africa', 'Asia','Canada')
and product in ('Sandal','Slipper');
run;


proc report data=shoes nowd;
column Product Region, sales;
define product/group;
define region/across;
define sales/'sales in USD';
rbreak after/summarize;
compute after;
Product = 'Grand Total';
endcomp;
run;

	Region
	Africa	Asia	Canada
Product	sales in USD	sales in USD	sales in USD
Sandal	$190,409	$8,208	$14,798
Slipper	$337,076	$152,032	$952,751
Grand Total	$527,485	$160,240	$967,549

Is there a way to dsiplay the percentage of the grand total at the bottom just undeneath of Grand Total such as this

	Region
	Africa	Asia	Canada
Product	sales in USD	sales in USD	sales in USD
Sandal	$190,409	$8,208	$14,798
Slipper	$337,076	$152,032	$952,751
Grand Total	$527,485	$160,240	$967,549

	Africa Pct	Asia Pct	Canada Pct
	36.10%	5.12%	1.53%

Re: Display percentage of Grand total per column in Proc Report

Cynthia_sas — Wed, 26 May 2021 18:12:28 GMT

Hi:

I'm confused by your percents. When I do the math with my calculator based on grand total of $1,655,274 , here's what I get:

This is probably easier to do in TABULATE, where you can generate ROWPCTSUM and COLPCTSUM more easily. PROC REPORT would only do the equivalent of the column percent and you'd need to fiddle to get an extra line underneath the grand total line.

How did you calculate the percents in your example?

Cynthia

Re: Display percentage of Grand total per column in Proc Report

Ksharp — Thu, 27 May 2021 13:05:34 GMT

Proc report is unable to do. But PROC SQL could.

data shoes;
set sashelp.shoes;
if region in ('Africa', 'Asia','Canada')
and product in ('Sandal','Slipper');
run;

proc sql;
create table want as
select 1 as id, product,region,put(sum(sales),dollar32. -l) as sales length=80
 from shoes
  group by product,region
union all
select 2 ,'Grand Total',region,put(sum(sales),dollar32. -l) as sales
 from shoes
  group by region
union all
select 3,'Percent',region,put(sum(sales)/(select sum(sales) from shoes),percent8.2 -l)
 from shoes
  group by region
;
quit;
proc report data=want nowd;
columns id product sales,region;
define id/group noprint;
define product/group;
define region/across;
define sales/group ' ';
run;

Re: Display percentage of Grand total per column in Proc Report

Cynthia_sas — Thu, 27 May 2021 16:19:26 GMT

Hi:

PROC REPORT can do this in a COMPUTE block:

I think it is easier to do in PROC TABULATE:

But it is do-able in PROC REPORT. It would take a bit more modification to "pretty up" both the REPORT and the TABULATE output. Probably another COMPUTE block if you want the percent underneath the totals in the REPORT output. But TABULATE can give you that with the procedure syntax, which is why I'd choose TABULATE for this one.

Cynthia

Re: Display percentage of Grand total per column in Proc Report

Ksharp — Fri, 28 May 2021 12:02:28 GMT

Cynthia,
How to make percent format is xx.xx% . I tried " all*pctsum*f=percent8.2, " , but can't get job done .

Re: Display percentage of Grand total per column in Proc Report

Ksharp — Fri, 28 May 2021 12:05:02 GMT

data shoes;
set sashelp.shoes;
if region in ('Africa', 'Asia','Canada')
and product in ('Sandal','Slipper');
run;

proc format;
picture fmt
low-high='009.99%'
;
run;
proc tabulate data=shoes;
class region product;
var sales;
tables product*sum all all*pctsum*f=fmt.,
       sales*region sales*all;
	run;

OK. I know how to make it happen.

Re: Display percentage of Grand total per column in Proc Report

Cynthia_sas — Fri, 28 May 2021 14:29:56 GMT

Hi:
The reason you need a PICTURE format is that the PERCENT format does a multiply by 100. The issue with this and TABULATE is that TABULATE also does a multiply by 100 for the percent-based statistics. So you don't want the PERCENT format to do a second multiply. The PICTURE format allows you to add the % sign without doing the multiply by 100 for the percent with TABULATE.
Cynthia