%macro utl_htmrtf2xlsdoc
   (
    utl_title=Using ODS Style and Proc Report to create MS-Excel/Word Objects,
    utl_obj=utl_htmrtf2xlsdoc,

    /*------------------------------------------------------------*\
    |  Inputs - None all self contained                            |
    \*------------------------------------------------------------*/

    /*------------------------------------------------------------*\
    | Process                                                      |
    \*------------------------------------------------------------*/

    Utl_Opts=%str(ls=120 ps=67),

    Utl_Styl=%str(font_style=roman font_face='Times New Roman'
              background=white foreground=black), /* Common Elements */

    Utl_ODSOt01=html,  /* either html or rtf           */
                       /* rtf for word with pagination */
                       /* html for Excel               */

    /*------------------------------------------------------------*\
    | Outputs                                                      |
    \*------------------------------------------------------------*/

    utl_otflt01=c:\utl\&utl_obj..&utl_ODSOt01,

    utlflop=0101010101

   ) / des = "Using ODS Style and Proc Report to create MS-Excel/Word Objects";


   /*------------------------------------------------------------*\
   |                                                              |
   | SAS Version 8.2 with most recent maintenence (09DEC01)       |
   |                                                              |
   | MS-Word and MS-Excel 2000 with all then service packs        |
   |                                                              |
   | I needed to create MS Word Docs and  MS Excel Sheets         |
   | that were very customizable.                                 |
   |                                                              |
   | You need to open the html file in Excel and the rtf          |
   | in WOrd to see what I mean.                                  |
   |                                                              |
   | I had problems with styles & templates, especially with      |
   | title and footers that would not be part of the              |
   | Excel table or the Word Table. I could not figure out        |
   | how to get titles and footnotes to span columns.             |
   |                                                              |
   | I examined several procedures for creating customizable      |
   | MS-Word and MS-Excel output an settled on Proc Report.       |
   |                                                              |
   | The rtf (MS-Word) has nice pagination and titles.            |
   | The _page_ variable in proc report allows me                 |
   | a 'title' like line ( or footnote ) that has correct         |
   | pagination and is part of the word table. The same           |
   | is true of Excel html file ( could use xls extension ).      |
   |                                                              |
   \*------------------------------------------------------------*/

   /*------------------------------------------------------------*\
   |                                                              |
   |                                                              |
   | Output                                                       |
   |                                                              |
   | Here is 'listing' of the DOC or Excel Output                 |
   |                                                              |
   | City by Dept by Title                                        |
   |   City      Dept         Title        Employees              |
   |   Boston    SalesMarket  SalesRepres         88              |
   |             SalesMarket  Scientist           42              |
   |             SalesMarket  Programmer          89              |
   |             SalesMarket  Manger              71              |
   |             R&D          SalesRepres         36              |
   |             R&D          Scientist            2              |
   |             R&D          Programmer           0              |
   |             R&D          Manger              28              |
   |             Support      SalesRepres         70              |
   |             Support      Scientist           20              |
   |             Support      Programmer          21              |
   |             Support      Manger              76              |
   |             Contract     SalesRepres         68              |
   |             Contract     Scientist           83              |
   |             Contract     Programmer          32              |
   |             Contract     Manger              72              |
   |             HR           SalesRepres         12              |
   |             HR           Scientist           25              |
   |             HR           Programmer          68              |
   |             HR           Manger              17              |
   |   Boston                                    920              |
   |                                                              |
   \*------------------------------------------------------------*/

   /*------------------------------------------------------------*\
   |  Create the data                                             |
   \*------------------------------------------------------------*/

data
    utl_htmrtf2xlsdoc01;

    do City = 'New York', 'Chicago', 'Boston', 'Portland', 'Hartford';

       do  Dept = 'SalesMarket', 'R&D', 'Support', 'Contract', 'HR';

         do Title= 'SalesRepres', 'Scientist', 'Programmer', 'Manger';

            Employees=int ( 100*uniform(-1) );
            Output;

         end;

       end;

    end;

run;


ods &utl_ODSOt01 body="&utl_OtFlt01";

options &utl_opts;run;

title;
footnote;
run;

proc report

   data=utl_htmrtf2xlsdoc01

   nowd

   style(header) =[ &Utl_Styl font_size=4 ]

   style(column) =[ &Utl_Styl font_size=3 ]

   style(lines)  =[ &Utl_Styl font_size=6 ]

   style(summary)=[&Utl_Styl foreground=cx3e3d73 background=cxaeadd9
                   font_size=3 just=r];

   columns
           City
           Dept
           Title
           Employees
   ;

   define City      / order    style(column)=[font_style=italic];

   define Dept      / display ;

   define Title     / display ;

   define Employees / analysis sum;

   break after City / summarize;

   compute before _page_ ;

     Line @1 "City by Dept by Title";

   endcomp;

   compute employees;

      if employees.sum>70 and _break_=' ' then

      call define(_col_, "style",
                  "style=[ &Utl_Styl foreground=cx3e3d73 background=cxaeadd9
                          font_weight=bold]");
   endcomp;



run;
quit;

ods &Utl_ODSot01 close;
run;

%mend utl_htmrtf2xlsdoc;


%utl_htmrtf2xlsdoc;