The SAS Output Delivery System and reporting techniques

Need help to create tabular type report

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Need help to create tabular type report

[ Edited ]

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.

 

screenshot_20170319_080356.jpg


Accepted Solutions
Solution
‎03-28-2017 02:03 PM
SAS Super FREQ
Posts: 8,743

Re: Need help to create tabular type report

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):

code_depends_data.png

 

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;

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Need help to create tabular type report

Proc Report, Proc Tabulate. 

 

 

Super User
Posts: 9,676

Re: Need help to create tabular type report

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;


Super User
Posts: 9,676

Re: Need help to create tabular type report



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;


Valued Guide
Posts: 505

Re: Need help to create tabular type report

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;
Contributor
Posts: 31

Re: Need help to create tabular type report

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

  

Super User
Posts: 17,819

Re: Need help to create tabular type report


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.

Valued Guide
Posts: 505

Re: Need help to create tabular type report

 

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;

 

Solution
‎03-28-2017 02:03 PM
SAS Super FREQ
Posts: 8,743

Re: Need help to create tabular type report

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):

code_depends_data.png

 

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;
Valued Guide
Posts: 505

Re: Need help to create tabular type report

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?

SAS Super FREQ
Posts: 8,743

Re: Need help to create tabular type report

Hi:

My CNTR temporary variable is a cumulative variable. Temporary variables are automatically retained, so I have seen them used to replicate PROC FREQ reports with cum totals and cum percents and to generate % of subgroup totals.

I am still a big fan of pre-processing with a DATA step for more complex reports. I agree, that there is a LOT you can do in a DATA step that can make your life easier for subsequent reporting.

And, with the new Report Writing Interface, you can directly generate a report as your do your processing. However for a report as simple as the one that was posted, my first choice would be to do it with PROC REPORT. But, DATA step would be my second choice if there were other, more complex requirements.

cynthia
Valued Guide
Posts: 505

Re: Need help to create tabular type report

Good points.

 

I agree with you Cynthia, pretty straight forward 'summarize' type report.

 

I did hesitate with the DOW loop.

SAS Super FREQ
Posts: 8,743

Re: Need help to create tabular type report

I teach so many beginner classes and see people using techniques that they don't understand and don't know how to apply correctly. So when I post here or teach my classes, I tend to stick with simpler examples and keep the advanced techniques for when they are really needed.

cynthia

(But, I think it's good for people to see that there's always more than one way to accomplish your goal with SAS -- everything from brute force if statements to using CNTLIN to make your own user-define formats to avoid the IF statements to using nested loops, arrays and even DOW loops. That is the totally cool thing about the Forum is the collective breadth of experience.)
Contributor
Posts: 31

Re: Need help to create tabular type report

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

Contributor
Posts: 31

Re: Need help to create tabular type report

Thanks for your suggestion. I have one query, attaching .pdf file to email is easy. But here thing is report should display in email with out any attachments. Any suggestions here??
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 408 views
  • 1 like
  • 5 in conversation