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) |
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.
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.
Thank you! Proc Tabulate is interesting...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.