Hi,
I need your help to create below report. I have created list reports but not created below type of report. So please help me on this.
How can I create below report in SAS using any procedure.Thanks In advance.
Hi:
This example is a slightly different version of Ksharp's PROC REPORT example. The difference is that I arranged the report row differently so that a macro variable for the blank line is not required. Note that the LEFTMARGIN technique is only supported by RTF and PDF, as shown in the comparison of the 3 outputs. By putting the CENTER variable first on the report row, I could do a simple LINE in the COMPUTE after CENTER. My code uses similar techniques, but instead of using SASHELP.CLASS, I made some fake data. Of course, the structure of the data will make a difference -- for my code to work, the assumption is that CENTER and SALEGRP are present on every row -- so that GROUP usage in PROC REPORT works as designed.
Here's the output produced by my program in HTML, RTF and PDF (I annotated the major features of the break processing):
and, here's the code that produced the above output, including making the data.
cynthia
** the code;
data sales;
length SaleGrp $30 Center 8 SaleType $30 Amount 8;
infile datalines dlm=',' dsd;
input Salegrp $ Center SaleType $ Amount;
datalines;
"Clothing Sales",1612,"Online Sale",1700
"Clothing Sales",1612,"Offline Sale",1300
"Electronics Sales",1612,"Electronics1",.
"Electronics Sales",1612,"Electronics2",.
"Electronics Sales",1612,"Electronics3",.
"Footware Sales",1612,"Stall1",2000
"Footware Sales",1612,"Stall2",2500
;
run;
title; footnote;
ods html(id=1) file='c:\temp\sale_report.html';
ods rtf(id=2) file='c:\temp\sale_report.rtf';
ods pdf(id=3) file='c:\temp\sale_report.pdf';
options missing=' ';
proc report data=sales spanrows
style(summary)={fontweight=bold};
title j=l bold 'Title: Total sales calculator February 2017';
column center SaleGrp SaleType Amount dcenter usecntr;
define center / group noprint;
define SaleGrp / group noprint;
define SaleType / group 'Sales Overview';
define Amount / sum 'Amount';
define dCenter / computed 'Center Code' style(column)={just=l};
define usecntr / computed noprint;
compute before _page_ / style=Header{just=l};
line 'Total sales calculator February 2017';
endcomp;
compute before;
** make a temporary variable whose value will be retained across all report rows;
** this means that on the first report row, the value of CNTR will be 0;
** that will be the first break for SALEGRP variable;
cntr=0;
endcomp;
rbreak after / summarize;
compute after;
SaleType = 'Total Sales';
endcomp;
break before SaleGrp / summarize;
compute before SaleGrp / style={just=l};
SaleType = catx(' ',SaleGrp,'Total');
endcomp;
compute after center;
line ' ';
endcomp;
compute usecntr;
** this "dummy" variable will be used to control writing;
** the value of Center only one time;
usecntr = cntr;
if usecntr = 0 then dcenter=center;
else dcenter=.;
cntr+1;
endcomp;
compute saletype;
** leftmargin will only work for RTF and PDF;
if _break_ = ' ' then
call define(_col_,'style','style={leftmargin=.25in}');
endcomp;
run;
ods html(id=1) close;
ods rtf(id=2) close;
ods pdf(id=3) close;
Proc Report, Proc Tabulate.
How about this one . data class; retain CenterCode '1160'; set sashelp.class; run; proc sort data=class; by centercode sex; run; data _null_; set class end=last; if last then call symputx('last',sex); run; title1 'xxxxxxxxx'; title2 ' '; proc report data=class nowd out=temp; column CenterCode sex name weight x; define CenterCode/group noprint; define sex/group noprint; define name/display style={pretext=' ' marginleft=2cm}; define weight/analysis sum; define x/computed 'Center Code'; compute x/char length=20; if lowcase(_break_)='sex' then count+1; if count=1 then do;x=centercode;count=9999;end; if lowcase(_break_)='_rbreak_' then name='TOTAL'; endcomp; compute before sex; if lowcase(_break_)='sex' then name=sex; endcomp; compute after sex; line=' '; if sex="&last" then len=10; else len=0; line line $varying10. len; endcomp; break before sex/summarize style={fontweight=bold fontsize=3}; rbreak after /summarize style={fontweight=bold fontsize=3}; run;
data class; retain CenterCode '1160'; set sashelp.class; run; proc sort data=class; by centercode sex; run; data _null_; set class end=last; if last then call symputx('last',sex); run; title1 'xxxxxxxxx'; title2 ' '; proc report data=class nowd out=temp; column CenterCode sex name weight x; define CenterCode/group noprint; define sex/group noprint; define name/display style={pretext=' ' marginleft=2cm}; define weight/analysis sum; define x/computed 'Center Code'; compute x/char length=20; if lowcase(_break_)='sex' then count+1; if count=1 then do;x=centercode;count=9999;end; if lowcase(_break_)='_rbreak_' then name='TOTAL'; endcomp; compute before sex; name=sex; endcomp; compute after sex; line=' '; if sex="&last" then len=10; else len=0; line line $varying10. len; endcomp; break before sex/summarize style={fontweight=bold fontsize=3}; rbreak after /summarize style={fontweight=bold fontsize=3}; run;
Need help to create tabular type report
see for rtf output;
https://www.dropbox.com/s/stvea5zojvzg7e6/prerpt.rtf?dl=0
I prefer to pre-process data before report and
minimize the use of compute blocks. Especially if
I feel management may ask for changes.
You can get very close to the ops output with just my
input and report without preprocessing, but that is a
less flexible method?
inspired by
https://goo.gl/w4KKqr
https://communities.sas.com/t5/ODS-and-Base-Reporting/Need-help-to-create-tabular-type-report/m-p/342315
HAVE
====
Up to 40 obs WORK.HAVE total obs=7
Obs SALES DEPT AMOUNT CODE
1 Online Sale Clothing 1700 1612
2 Offline Sale Clothing 1300 .
3 Electronics1 Electronics . .
4 Electronics2 Electronics . .
5 Electronics3 Electronics . .
6 Stall1 Footware 2000 .
7 Stall2 Footware 1500 .
WANT
====
Total sales calculator February 2017 => Bold 12pt
Sales Overview Amount Center Code =Bold 10pt
Clothing Sales Total $3,000 1612 => Bold
Online Sale $1,700
Offline Sale $1,300
Electronics Sales Total => Bold
Electronics1
Electronics2
Electronics3
=> Bold
Footware Sales Total $3,500
Stall1 $2,000
Stall2 $1,500
=> Bold
Total $6,500
* create the data;
Using google 'free' tesseract to convert the ops pictute to data;
* print screen and save as a png and use google tesseract to convert to text;
x c:/progra~2/tesseract-ocr/tesseract d:/png/prepro.png d:/txt/prepro.txt;
* I useed Google te
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;
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(' ','\b',dept,'Sales Total \b0');
output;
do until (last.dept);
set have end=dne;
by dept notsorted;
code=.;
sales=catx(' ','\li360',sales);
output;
end;
cde=.;
tot=0;
keep sales amount code;
if dne then do;
amount=grantot;
savsales=sales;
savamount=amount;
sales=.;
amount=.;
output;
sales='\b Total \b0';
savsales=savsales;
amount=savamount;
output;
end;
run;quit;
/*
Up to 40 obs WORK.PRERPT total obs=12
Obs SALES AMOUNT CODE
1 \b Clothing Sales Total \b0 3000 1612
2 \li360 Online Sale 1700 .
3 \li360 Offline Sale 1300 .
4 \b Electronics Sales Total \b0 0 .
5 \li360 Electronics1 . .
6 \li360 Electronics2 . .
7 \li360 Electronics3 . .
8 \b Footware Sales Total \b0 3500 .
9 \li360 Stall1 2000 .
10 \li360 Stall2 1500 .
11 . . .
12 \b Total \b0 6500 .
*/
* macro on end;
%utl_rtflan100;
options orientation=portrait;
ods escapechar='^';
ods rtf file="d:/rtf/prerpt.rtf" style=utl_rtflan100;
proc report data=prerpt nowd style(header)={font_weight=bold font_size=10pt just=left};
cols ("^S={just=l font_weight=bold font_size=12pt} Total sales calculator February 2017" sales amount code);
define sales / display "Sales Overview" left;
define amount / display "Amount" center format=dollar8.;
define code / display "Center Code" center;
run;quit;
ods rtf close;
%Macro utl_rtflan100
(
style=utl_rtflan100,
frame=box,
rules=groups,
bottommargin=1.0in,
topmargin=1.5in,
rightmargin=1.0in,
cellheight=10pt,
cellpadding = 7,
cellspacing = 3,
leftmargin=.75in,
borderwidth = 1
) / Des="SAS Rtf Template for CompuCraft";
options orientation=landscape;run;quit;
ods path work.templat(update) sasuser.templat(update) sashelp.tmplmst(read);
Proc Template;
define style &Style;
parent=styles.rtf;
replace body from Document /
protectspecialchars=off
asis=on
bottommargin=&bottommargin
topmargin =&topmargin
rightmargin =&rightmargin
leftmargin =&leftmargin
;
replace color_list /
'link' = blue
'bgH' = _undef_
'fg' = black
'bg' = _undef_;
replace fonts /
'TitleFont2' = ("Arial, Helvetica, Helv",11pt,Bold)
'TitleFont' = ("Arial, Helvetica, Helv",11pt,Bold)
'HeadingFont' = ("Arial, Helvetica, Helv",10pt)
'HeadingEmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'StrongFont' = ("Arial, Helvetica, Helv",10pt,Bold)
'EmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'FixedFont' = ("Courier New, Courier",9pt)
'FixedEmphasisFont' = ("Courier New, Courier",9pt,Italic)
'FixedStrongFont' = ("Courier New, Courier",9pt,Bold)
'FixedHeadingFont' = ("Courier New, Courier",9pt,Bold)
'BatchFixedFont' = ("Courier New, Courier",7pt)
'docFont' = ("Arial, Helvetica, Helv",10pt)
'FootFont' = ("Arial, Helvetica, Helv", 9pt)
'StrongFootFont' = ("Arial, Helvetica, Helv",8pt,Bold)
'EmphasisFootFont' = ("Arial, Helvetica, Helv",8pt,Italic)
'FixedFootFont' = ("Courier New, Courier",8pt)
'FixedEmphasisFootFont'= ("Courier New, Courier",8pt,Italic)
'FixedStrongFootFont' = ("Courier New, Courier",7pt,Bold);
replace GraphFonts /
'GraphDataFont' = ("Arial, Helvetica, Helv",8pt)
'GraphAnnoFont' = ("Arial, Helvetica, Helv",8pt)
'GraphValueFont' = ("Arial, Helvetica, Helv",10pt)
'GraphUnicodeFont' = ("Arial, Helvetica, Helv",10pt)
'GraphLabelFont' = ("Arial, Helvetica, Helv",10pt,Bold)
'GraphLabel2Font' = ("Arial, Helvetica, Helv",10pt,Bold)
'GraphFootnoteFont' = ("Arial, Helvetica, Helv",8pt)
'GraphTitle1Font' = ("Arial, Helvetica, Helv",11pt,Bold)
'GraphTitleFont' = ("Arial, Helvetica, Helv",11pt,Bold);
style table from table /
outputwidth=100%
protectspecialchars=off
asis=on
background = colors('tablebg')
frame=&frame
rules=&rules
cellheight = &cellheight
cellpadding = &cellpadding
cellspacing = &cellspacing
bordercolor = colors('tableborder')
borderwidth = &borderwidth;
replace Footer from HeadersAndFooters
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off ;
replace FooterFixed from Footer
/ font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off;
replace FooterEmpty from Footer
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off;
replace FooterEmphasis from Footer
/ font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off;
replace FooterEmphasisFixed from FooterEmphasis
/ font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off;
replace FooterStrong from Footer
/ font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off;
replace FooterStrongFixed from FooterStrong
/ font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off;
replace RowFooter from Footer
/ font = fonts('FootFont') asis=on protectspecialchars=off just=left;
replace RowFooterFixed from RowFooter
/ font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmpty from RowFooter
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmphasis from RowFooter
/ font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmphasisFixed from RowFooterEmphasis
/ font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterStrong from RowFooter
/ font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterStrongFixed from RowFooterStrong
/ font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off;
replace SystemFooter from TitlesAndFooters / asis=on
protectspecialchars=off just=left;
end;
run;
quit;
%Mend utl_rtflan100;
Hi,
As per your reply, Below have mentioned my require data. And also mention few challenges over here.
1. I needs to export each sales category summary info into seperate tabs of excel sheet based on sales category and center code(each sales code has its own center code).
2. Last tab in excel sheet should have below mentioed report,
3. Is there any way to keep history of report by tracking every month data by appending current month data into previous month report,so that report should have all the previous month and current month data.
4. Is there any way to keep below report as it is email, Need your .pdf file report in mail body.If we open the mail , anbody can see the report with out opening anything.
Your suggestions are very helpful for me. Thanks in advance.
WANT
====
Total sales calculator February 2017 => Bold 12pt
Sales Overview Amount Center Code =Bold 10pt
Clothing Sales Total $3,000 1612 => Bold
Online Sale $1,700
Offline Sale $1,300
Electronics Sales Total => Bold
Electronics1
Electronics2
Electronics3
=> Bold
Footware Sales Total $3,500
Stall1 $2,000
Stall2 $1,500
=> Bold
Total $6,500
1. I needs to export each sales category summary info into separate tabs of excel sheet based on sales category and center code(each sales code has its own center code).
If you're on SAS 9.4 M3 look at ODS EXCEL, if not look at tagsets.excelxp. Tagsets will generate an XML not XLSX file though and you'll need to convert it. See the Data Driven Worksheet issue here: https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
2. Last tab in excel sheet should have below mentioned report,
Not sure what the difficulty here is. If you're generating this via PROC REPORT then use the PROC REPORT code with the solutions suggested above.
3. Is there any way to keep history of report by tracking every month data by appending current month data into previous month report,so that report should have all the previous month and current month data.
Sure. Create a library to store the data and append the data every month. You'll want to add a check to ensure that you don't accidentally double up a months data. Then the automated procedure will flow through so it doesn't matter how many months you have. To have a more 'automated' way of doing this you would SAS Data Integration Studio, so otherwise you have to code this step manually.
4. Is there any way to keep below report as it is email, Need your .pdf file report in mail body.If we open the mail , anybody can see the report with out opening anything.
Yes, in addition to creating an Excel file, you could create a PDF report and then attach that in the email.
http://www2.sas.com/proceedings/forum2008/038-2008.pdf
All of these are possible but they are work, and probably way more than I'd be willing to do in a forum as help.
I would suggest starting small and working through some of the examples you can find online to generate your reports. Then post questions as you run into specific issues.
Lexjansen.com will have many examples of creating reports, Cynthia Zender has some great papers on the topic as well.
Need help to create tabular type report
see for rtf output;
https://www.dropbox.com/s/stvea5zojvzg7e6/prerpt.rtf?dl=0
I prefer to pre-process data before report and
minimize the use of compute blocks. Especially if
I feel management may ask for changes.
You can get very close to the ops output with just my
input and report without preprocessing, but that is a
less flexible method?
inspired by
https://goo.gl/w4KKqr
https://communities.sas.com/t5/ODS-and-Base-Reporting/Need-help-to-create-tabular-type-report/m-p/342315
HAVE
====
Up to 40 obs WORK.HAVE total obs=7
Obs SALES DEPT AMOUNT CODE
1 Online Sale Clothing 1700 1612
2 Offline Sale Clothing 1300 .
3 Electronics1 Electronics . .
4 Electronics2 Electronics . .
5 Electronics3 Electronics . .
6 Stall1 Footware 2000 .
7 Stall2 Footware 1500 .
WANT
====
Total sales calculator February 2017 => Bold 12pt
Sales Overview Amount Center Code =Bold 10pt
Clothing Sales Total $3,000 1612 => Bold
Online Sale $1,700
Offline Sale $1,300
Electronics Sales Total => Bold
Electronics1
Electronics2
Electronics3
=> Bold
Footware Sales Total $3,500
Stall1 $2,000
Stall2 $1,500
=> Bold
Total $6,500
* create the data;
Using google 'free' tesseract to convert the ops pictute to data;
* print screen and save as a png and use google tesseract to convert to text;
x c:/progra~2/tesseract-ocr/tesseract d:/png/prepro.png d:/txt/prepro.txt;
* I useed Google te
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;
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(' ','\b',dept,'Sales Total \b0');
output;
do until (last.dept);
set have end=dne;
by dept notsorted;
code=.;
sales=catx(' ','\li360',sales);
output;
end;
cde=.;
tot=0;
keep sales amount code;
if dne then do;
amount=grantot;
savsales=sales;
savamount=amount;
sales=.;
amount=.;
output;
sales='\b Total \b0';
savsales=savsales;
amount=savamount;
output;
end;
run;quit;
/*
Up to 40 obs WORK.PRERPT total obs=12
Obs SALES AMOUNT CODE
1 \b Clothing Sales Total \b0 3000 1612
2 \li360 Online Sale 1700 .
3 \li360 Offline Sale 1300 .
4 \b Electronics Sales Total \b0 0 .
5 \li360 Electronics1 . .
6 \li360 Electronics2 . .
7 \li360 Electronics3 . .
8 \b Footware Sales Total \b0 3500 .
9 \li360 Stall1 2000 .
10 \li360 Stall2 1500 .
11 . . .
12 \b Total \b0 6500 .
*/
* macro on end;
%utl_rtflan100;
options orientation=portrait;
ods escapechar='^';
ods rtf file="d:/rtf/prerpt.rtf" style=utl_rtflan100;
proc report data=prerpt nowd style(header)={font_weight=bold font_size=10pt just=left};
cols ("^S={just=l font_weight=bold font_size=12pt} Total sales calculator February 2017" sales amount code);
define sales / display "Sales Overview" left;
define amount / display "Amount" center format=dollar8.;
define code / display "Center Code" center;
run;quit;
ods rtf close;
%Macro utl_rtflan100
(
style=utl_rtflan100,
frame=box,
rules=groups,
bottommargin=1.0in,
topmargin=1.5in,
rightmargin=1.0in,
cellheight=10pt,
cellpadding = 7,
cellspacing = 3,
leftmargin=.75in,
borderwidth = 1
) / Des="SAS Rtf Template for CompuCraft";
options orientation=landscape;run;quit;
ods path work.templat(update) sasuser.templat(update) sashelp.tmplmst(read);
Proc Template;
define style &Style;
parent=styles.rtf;
replace body from Document /
protectspecialchars=off
asis=on
bottommargin=&bottommargin
topmargin =&topmargin
rightmargin =&rightmargin
leftmargin =&leftmargin
;
replace color_list /
'link' = blue
'bgH' = _undef_
'fg' = black
'bg' = _undef_;
replace fonts /
'TitleFont2' = ("Arial, Helvetica, Helv",11pt,Bold)
'TitleFont' = ("Arial, Helvetica, Helv",11pt,Bold)
'HeadingFont' = ("Arial, Helvetica, Helv",10pt)
'HeadingEmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'StrongFont' = ("Arial, Helvetica, Helv",10pt,Bold)
'EmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'FixedFont' = ("Courier New, Courier",9pt)
'FixedEmphasisFont' = ("Courier New, Courier",9pt,Italic)
'FixedStrongFont' = ("Courier New, Courier",9pt,Bold)
'FixedHeadingFont' = ("Courier New, Courier",9pt,Bold)
'BatchFixedFont' = ("Courier New, Courier",7pt)
'docFont' = ("Arial, Helvetica, Helv",10pt)
'FootFont' = ("Arial, Helvetica, Helv", 9pt)
'StrongFootFont' = ("Arial, Helvetica, Helv",8pt,Bold)
'EmphasisFootFont' = ("Arial, Helvetica, Helv",8pt,Italic)
'FixedFootFont' = ("Courier New, Courier",8pt)
'FixedEmphasisFootFont'= ("Courier New, Courier",8pt,Italic)
'FixedStrongFootFont' = ("Courier New, Courier",7pt,Bold);
replace GraphFonts /
'GraphDataFont' = ("Arial, Helvetica, Helv",8pt)
'GraphAnnoFont' = ("Arial, Helvetica, Helv",8pt)
'GraphValueFont' = ("Arial, Helvetica, Helv",10pt)
'GraphUnicodeFont' = ("Arial, Helvetica, Helv",10pt)
'GraphLabelFont' = ("Arial, Helvetica, Helv",10pt,Bold)
'GraphLabel2Font' = ("Arial, Helvetica, Helv",10pt,Bold)
'GraphFootnoteFont' = ("Arial, Helvetica, Helv",8pt)
'GraphTitle1Font' = ("Arial, Helvetica, Helv",11pt,Bold)
'GraphTitleFont' = ("Arial, Helvetica, Helv",11pt,Bold);
style table from table /
outputwidth=100%
protectspecialchars=off
asis=on
background = colors('tablebg')
frame=&frame
rules=&rules
cellheight = &cellheight
cellpadding = &cellpadding
cellspacing = &cellspacing
bordercolor = colors('tableborder')
borderwidth = &borderwidth;
replace Footer from HeadersAndFooters
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off ;
replace FooterFixed from Footer
/ font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off;
replace FooterEmpty from Footer
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off;
replace FooterEmphasis from Footer
/ font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off;
replace FooterEmphasisFixed from FooterEmphasis
/ font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off;
replace FooterStrong from Footer
/ font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off;
replace FooterStrongFixed from FooterStrong
/ font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off;
replace RowFooter from Footer
/ font = fonts('FootFont') asis=on protectspecialchars=off just=left;
replace RowFooterFixed from RowFooter
/ font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmpty from RowFooter
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmphasis from RowFooter
/ font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmphasisFixed from RowFooterEmphasis
/ font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterStrong from RowFooter
/ font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterStrongFixed from RowFooterStrong
/ font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off;
replace SystemFooter from TitlesAndFooters / asis=on
protectspecialchars=off just=left;
end;
run;
quit;
%Mend utl_rtflan100;
Hi:
This example is a slightly different version of Ksharp's PROC REPORT example. The difference is that I arranged the report row differently so that a macro variable for the blank line is not required. Note that the LEFTMARGIN technique is only supported by RTF and PDF, as shown in the comparison of the 3 outputs. By putting the CENTER variable first on the report row, I could do a simple LINE in the COMPUTE after CENTER. My code uses similar techniques, but instead of using SASHELP.CLASS, I made some fake data. Of course, the structure of the data will make a difference -- for my code to work, the assumption is that CENTER and SALEGRP are present on every row -- so that GROUP usage in PROC REPORT works as designed.
Here's the output produced by my program in HTML, RTF and PDF (I annotated the major features of the break processing):
and, here's the code that produced the above output, including making the data.
cynthia
** the code;
data sales;
length SaleGrp $30 Center 8 SaleType $30 Amount 8;
infile datalines dlm=',' dsd;
input Salegrp $ Center SaleType $ Amount;
datalines;
"Clothing Sales",1612,"Online Sale",1700
"Clothing Sales",1612,"Offline Sale",1300
"Electronics Sales",1612,"Electronics1",.
"Electronics Sales",1612,"Electronics2",.
"Electronics Sales",1612,"Electronics3",.
"Footware Sales",1612,"Stall1",2000
"Footware Sales",1612,"Stall2",2500
;
run;
title; footnote;
ods html(id=1) file='c:\temp\sale_report.html';
ods rtf(id=2) file='c:\temp\sale_report.rtf';
ods pdf(id=3) file='c:\temp\sale_report.pdf';
options missing=' ';
proc report data=sales spanrows
style(summary)={fontweight=bold};
title j=l bold 'Title: Total sales calculator February 2017';
column center SaleGrp SaleType Amount dcenter usecntr;
define center / group noprint;
define SaleGrp / group noprint;
define SaleType / group 'Sales Overview';
define Amount / sum 'Amount';
define dCenter / computed 'Center Code' style(column)={just=l};
define usecntr / computed noprint;
compute before _page_ / style=Header{just=l};
line 'Total sales calculator February 2017';
endcomp;
compute before;
** make a temporary variable whose value will be retained across all report rows;
** this means that on the first report row, the value of CNTR will be 0;
** that will be the first break for SALEGRP variable;
cntr=0;
endcomp;
rbreak after / summarize;
compute after;
SaleType = 'Total Sales';
endcomp;
break before SaleGrp / summarize;
compute before SaleGrp / style={just=l};
SaleType = catx(' ',SaleGrp,'Total');
endcomp;
compute after center;
line ' ';
endcomp;
compute usecntr;
** this "dummy" variable will be used to control writing;
** the value of Center only one time;
usecntr = cntr;
if usecntr = 0 then dcenter=center;
else dcenter=.;
cntr+1;
endcomp;
compute saletype;
** leftmargin will only work for RTF and PDF;
if _break_ = ' ' then
call define(_col_,'style','style={leftmargin=.25in}');
endcomp;
run;
ods html(id=1) close;
ods rtf(id=2) close;
ods pdf(id=3) close;
You can do all that processing in compute blocks, but what if your manager says I would like 'cumulative' totals along with the individual records or if he said I would like cumulative subtotals. Easy to do in the DOW loop process, not so easy in compute blocks?
Good points.
I agree with you Cynthia, pretty straight forward 'summarize' type report.
I did hesitate with the DOW loop.
Thank you so much Ksharp,Roger and Cynthia for all your time and guidance. It's a great place to learn new things. Your code teach me a lot of new things. Thanks for all and report design is not yet fixed. I will share you my challanges if any new. Thanks
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!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: