topic Re: Need help to create tabular type report in ODS and Base Reporting

Need help to create tabular type report

Banu — Sun, 19 Mar 2017 02:36:06 GMT

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.

Re: Need help to create tabular type report

Reeza — Sun, 19 Mar 2017 03:01:17 GMT

Proc Report, Proc Tabulate.

Re: Need help to create tabular type report

Ksharp — Sun, 19 Mar 2017 03:41:27 GMT

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;

Re: Need help to create tabular type report

Ksharp — Sun, 19 Mar 2017 03:44:56 GMT



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;

Re: Need help to create tabular type report

rogerjdeangelis — Sun, 19 Mar 2017 18:03:16 GMT

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;

Re: Need help to create tabular type report

rogerjdeangelis — Sun, 19 Mar 2017 18:04:28 GMT

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;

Re: Need help to create tabular type report

Cynthia_sas — Sun, 19 Mar 2017 20:40:00 GMT

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;

Re: Need help to create tabular type report

rogerjdeangelis — Mon, 20 Mar 2017 00:15:54 GMT

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?

Re: Need help to create tabular type report

Cynthia_sas — Tue, 21 Mar 2017 01:52:36 GMT

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

Re: Need help to create tabular type report

rogerjdeangelis — Tue, 21 Mar 2017 02:08:15 GMT

Good points.

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

I did hesitate with the DOW loop.

Re: Need help to create tabular type report

Cynthia_sas — Tue, 21 Mar 2017 02:24:24 GMT

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

Re: Need help to create tabular type report

Banu — Tue, 21 Mar 2017 03:02:29 GMT

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

Re: Need help to create tabular type report

Banu — Tue, 28 Mar 2017 18:02:01 GMT

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

Re: Need help to create tabular type report

Reeza — Tue, 28 Mar 2017 19:38:12 GMT

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.

Re: Need help to create tabular type report

Banu — Tue, 28 Mar 2017 23:30:32 GMT

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??

Re: Need help to create tabular type report

rogerjdeangelis — Tue, 28 Mar 2017 23:48:42 GMT

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;

Re: Need help to create tabular type report

Reeza — Tue, 28 Mar 2017 23:53:51 GMT

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.

Re: Need help to create tabular type report

Banu — Wed, 29 Mar 2017 01:03:13 GMT

Yes In body. IThank you. I will explore on this.