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)
... View more