Display percentage of Grand total per column in Proc Report

[Q1983]

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%

[Cynthia_sas]

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

[Ksharp]

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;

[Cynthia_sas · Accepted solution]

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

[Ksharp]

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

[Ksharp]

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.

[Cynthia_sas]

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