Need help to create tabular type report

[rogerjdeangelis]

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;

[Reeza]

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. 

[Banu]

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