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;
In body? Use ODS Listing or a text file to first customize Report with correct spaces and format and then insert into body of email using PUT statements. Guess and test is only way I know of to get the layout correct.
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.