BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rogerjdeangelis
Barite | Level 11
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
Super User

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
Obsidian | Level 7
Yes In body. IThank you. I will explore on this.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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