Proc report - ODS Excel

[DLROW]

Hi,  Below is the code and the sample output is attached. I need to know how to insert rows that calculates percentages for the across variables . Please advise. Thanks!

%let outFile = "C:\test.xlsx";
data mnthly_sales;
length Segment $ 20 ;
input Segment $ Month Customer;
/*label zip="Zip Code"
cty="County"
var="Variety"
sales="Monthly Sales";*/
datalines;
LargeGroupA 1 20000
LargeGroupA 2 3000
LargeGroupA 3 3500
LargeGroupB 1 20000
LargeGroupB 2 3000
LargeGroupB 3 3500
MidGroupA 1 20000
MidGroupA 2 3000
MidGroupA 3 3500
MidGroupB 1 20000
MidGroupB 2 3000
MidGroupB 3 3500
SmallGroupA 1 20000
SmallGroupA 2 3000
SmallGroupA 3 3500
SmallGroupB 1 20000
SmallGroupB 2 3000
SmallGroupB 3 3500

;

Proc Print data=mnthly_sales;
title "Raw Data";
run;

ods excel file=&Outfile.;
Proc report data=mnthly_sales nowd;

COLUMN ('Segment' Segment) Month,Customer ('Total' Customer=TOTAL) ;
define Segment /GROUP '' style(column)=[cellwidth=5.5in] ;
define Month /across '';
define Customer / analysis sum f=comma6. ' ';
define total / analysis sum '' f=comma6.;

rbreak after /summarize style(summary)={background=white foreground=black font_weight=bold};

compute after;
if upcase(_break_)= '_RBREAK_' then;
Segment="Total";
call define ('Segment','style','style=[background=ggr color=black font_style=italic just=left font_weight=bold]');
call define (_row_,'style','style=[font_style=italic just=left font_weight=bold]');
endcomp;
RUN;

OUTPUT :  I got the data until total. I have to insert percentage rows after that. (Formula is also given. Eg : B8 / E8 : B refers to column B and 8 refers to row number ( B, C, D , E are columns) 

Segment	1	2	3	Total
LargeGroupA	20,000	3,000	3,500	26,500
LargeGroupB	20,000	3,000	3,500	26,500
MidGroupA	20,000	3,000	3,500	26,500
MidGroupB	20,000	3,000	3,500	26,500
SmallGroupA	20,000	3,000	3,500	26,500
SmallGroupB	20,000	3,000	3,500	26,500
Total	120,000	18,000	21,000	159,000
All	B8 / E8 ( 75%)	C8 / E8	D8 / E8	E8 / E8
Large Group Percentages
Large GroupA	B2 / E2	C2 / E2	D2 / E2	B2 / E2
Large GroupB	B3 / E3	B4 / E3	B3 / E3	B3 / E3
Total Large	sum(B2:B3) / Sum(N2:N3)	sum(B2:B3) / Sum(N2:N3)	sum(B2:B3) / Sum(N2:N3)	sum(B2:B3) / Sum(N2:N3)
MidGroup Percentages
MidGroupA	B4 / E4	C4 / E4	D4 / E4	E4 / E4
MidGroupB	B5 / E5	C5 / E5	D5 / E5	E5 / E5
Total Mid	sum(B4:B5) / Sum(E4 : E5)	sum(B4:B5) / Sum(E4 : E5)	sum(B4:B5) / Sum(E4 : E5)	sum(B4:B5) / Sum(E4 : E5)
SmallGroup
SmallGroupA	B6 / E6	C6 / E6	D6 / E6	E6 / E6
SmallGroupB	B7 / E7	C7 / E7	D7 / E7	E7 / E7
Total Small	sum(B6:B7) / Sum(E6 : E7)	sum(B6:B7) / Sum(E6 : E7)	sum(B6:B7) / Sum(E6 : E7)	sum(B6:B7) / Sum(E6 : E7)

[PaigeMiller]

OUTPUT :  I got the data until total. I have to insert percentage rows after that. (Formula is also given. Eg : B8 / E8 : B refers to column B and 8 refers to row number ( B, C, D , E are columns) 

PROC REPORT does not operate as Excel does. It would be extremely difficult, if not impossible, in PROC REPORT to obtain the value of cell B8 and divide that by cell E8.

So the solution I use is to create these percentages that you want outside of PROC REPORT, probably in a data step, do the division, and then append the results to other data, in the order you want it. By placing the desired calculations in a data set, you have the full power of the SAS language to do the calculations, and the your only use of PROC REPORT is to generate the output (and not do any calculations).

Nevertheless, this will take a fair bit of programming to get the result in this exact form. I would use PROC SUMMARY to get the totals you need, then re-arrange the PROC SUMMARY output so you can do the division you want.

Or you might be able to re-arrange the table somehow so that less programming is needed.

[Reeza]

PROC TABULATE may work here instead.

[ballardw]

I find the table layout a tad odd. The following example has all of the elements though the groupings are bit different.

Admittedly your actual members of "LargeGroup" and such likely are more interestingly named.

data mnthly_sales;
length Segment $ 20 ;
input Segment $ Month Customer;
/*label zip="Zip Code"
cty="County"
var="Variety"
sales="Monthly Sales";*/
datalines;
LargeGroupA 1 20000
LargeGroupA 2 3000
LargeGroupA 3 3500
LargeGroupA 1 20000
LargeGroupB 2 3000
LargeGroupB 3 3500
MidGroupA 1 20000
MidGroupA 2 3000
MidGroupA 3 3500
MidGroupB 1 20000
MidGroupB 2 3000
MidGroupB 3 3500
SmallGroupA 1 20000
SmallGroupA 2 3000
SmallGroupA 3 3500
SmallGroupB 1 20000
SmallGroupB 2 3000
SmallGroupB 3 3500

;
proc format library=work;
value $segment (multilabel notsorted)
"LargeGroupA"="LargeGroupA"
"LargeGroupB"="LargeGroupB"
"MidGroupA"="MidGroupA"
"MidGroupB"="MidGroupB"
"SmallGroupA"="SmallGroupA"
"SmallGroupB"="SmallGroupB"
"LargeGroupA","LargeGroupB","MidGroupA","MidGroupB","SmallGroupA","SmallGroupB"='Total'
"LargeGroupA","LargeGroupB"='Large Total'
"MidGroupA","MidGroupB"='Mid Total'
"SmallGroupA","SmallGroupB"='Small Total'
;
run;



proc tabulate data=mnthly_sales;
   class segment /mlf preloadfmt order=data;
   format segment $segment.;
   class month;
   var customer;
   tables segment* customer=''*(sum rowpctsum='%') ,
          month all='Total'
          /row=float printmiss
;
run;

MLF (multilabel format), PRELOADFMT, Order, and printmiss plus the actual order of statements in the format definition can interact in interesting ways (plus the format option notsorted).

It may or may not be worth experimenting with. Sort order of the actual values of your segment variable could be interesting as well.

[DLROW]

Thank you! Proc Tabulate is interesting...