Here is the excel equivalent to my rtf report
see for ods excell output of report;
https://www.dropbox.com/s/18cp9gi2omchrnx/utl_100rpt.xlsx?dl=0
Data have;
*informat Sales & $16. Amount & 5. code $5.;
length sales $32;
infile datalines4 pad;
input @1 dept $12. Sales & $16. @29 Amount & 5. @46 code ;
datalines4;
Clothing Online Sale 1700 1612
Clothing Offline Sale 1300 .
Electronics Electronics1 . .
Electronics Electronics2 . .
Electronics Electronics3 . .
Footware Stall1 2000 .
Footware Stall2 1500 .
;;;;
run;quit;
* preprocess;
data prerpt;
retain begdot 0 grantot 0 tot 0 cde 0;
length sales $200;
do until (last.dept);
set have;
by dept notsorted;
tot=sum(tot,amount);
if code ne . then cde=code;
end;
code=cde;
amount=tot;
grantot=sum(grantot,tot);
sales=catx(' ','^S={fontweight=bold}^',dept,'Sales Total');
output;
do until (last.dept);
set have end=dne;
by dept notsorted;
code=.;
* hidden dragon;
sales=' '!!sales;
output;
end;
cde=.;
tot=0;
keep sales amount code;
if dne then do;
amount=grantot;
savsales=sales;
savamount=amount;
sales='';
amount=.;
output;
sales='^S={fontweight=bold}^ Total';
savsales=savsales;
amount=savamount;
output;
end;
run;quit;
/*
Up to 40 obs WORK.PRERPT total obs=12
Obs SALES AMOUNT CODE
1 ^S={fontweight=bold}^ Clothing Sales Total 3000 1612
2 Online Sale 1700
3 Offline Sale 1300
4 ^S={fontweight=bold}^ Electronics Sales Total 0
5 Electronics1
6 Electronics2
7 Electronics3
8 ^S={fontweight=bold}^ Footware Sales Total 3500
9 Stall1 2000
10 Stall2 1500
11
12 ^S={fontweight=bold}^ Total 6500
*/
* macro on end;
options missing=' ';
ods listing close;
%let fyl=d:/xls/utl_100rpt.xlsx;
ods excel file="&fyl" style=pearl
options
(
tab_color = "red"
autofilter = 'yes'
orientation = 'landscape'
zoom = "80"
suppress_bylines = 'no'
embedded_titles = 'yes'
embedded_footnotes = 'yes'
embed_titles_once = 'yes'
gridlines = 'yes'
frozen_headers = 'Yes'
frozen_rowheaders = 'yes'
);
;run;quit;
ods excel options(sheet_name="utl_100rpt" sheet_interval="none");
ods escapechar='^';
proc report data=prerpt nowd style(header)={font_weight=bold font_size=15pt just=left}
;
cols ("^S={just=l font_weight=bold font_size=15pt} Total Sales Calculator February 2017" sales amount code);
define sales / display "^S={font_size=13pt}Sales Overview" style(column)={font_weight=bold font_size=13pt just=left};
define amount / display "^S={font_size=13pt}Amount" style(column)={font_weight=bold font_size=13pt just=right} format=dollar8.;
define code / display "^S={font_size=13pt}Center Code" style(column)={font_weight=bold font_size=13pt just=center};
run;quit;
ods excel close;
ods listing;
