Can proc tabulate put a rule at the foot of a table when writing to the tagsets.excelxp destination

[AdrianGriffin]

A rule at the foot of the table helps define the table visually and reassures readers that what they see is the entire output.

I can do this in proc report thusly:

compute after/style=[bordertopwidth=1pt];
line ' ' ;
endcomp;

But I can’t make proc tabulate give any special treatment to the end of the table. If there was just one footnote line, I suppose I could define style systemfooter to have a top border, but then every footnote line would have the same top border. 

Here’s my code

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Generate some data;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

data Sales;

  do Year=2008 to 2012;

    do Salesman='Smith ', 'Jones', 'Valdez', 'Lee';

      do County='Alameda', 'Sonoma', 'Yolo', 'Lake', 'Merced';

        do Industry='Wineries', 'Packers';

          do Product='Pump   ', 'Hoist', 'Furnace', 'Fan';

             Sales=round(10000+ranuni(88)*10000, 10);

             Mileage=round(800+ranuni(88)*80);

             if ranuni(3)<0.8 then output;

          end;

        end;

      end;

    end;

  end;

attrib mileage format=comma11. label='Mileage reimbursed';

attrib sales format=comma11. label='Annual sales';

run;

proc sort data=sales; by salesman; run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*XML output suitable for Excel using tagsets.excel

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

/* Compile tagset Tagsets.ExcelXP_mod, which makes changes to the ExcelXP tagset to preserve the grid lines.       */

proc template;

   define tagset tagsets.excelxp_mod;

      parent=tagsets.excelxp;

      define event sub_body;

         break /if $sub_body;

            set $sub_body "True";

            set $body_class "_body";

            put '<Style ss:ID="_body">' nl;

            put ' <Font ss:FontName="Gill Sans MT" ss:Size="11" />' NL;

            put "<Interior />" Nl;

            put '</Style>' nl;

            set $contents_class "_contents";

            put '<Style ss:ID="_contents">' nl;

            put ' <Interior ss:Pattern="Solid" />' nl;

            put ' <Protection ss:Protected="1" />' nl;

            put '</Style>' nl;

            set $pages_class "_pages";

            put '<Style ss:ID="_pages">' nl;

            put ' <Interior ss:Pattern="Solid" />' nl;

            put ' <Protection ss:Protected="1" />' nl;

            put '</Style>' nl;

      end;

   end;

run;

proc template;

  *Style suitable for the plain look that I want;

  define style styles.excel_xml_test;

  parent=styles.minimal;

  style body/background=transparent topmargin=0.5in bottommargin=0.5in leftmargin=0.5in rightmargin=0.5in;

  style systemtitle/font_face='Gill Sans MT' font_size=12pt verticalalign=middle;

  style systemfooter/font_face='Gill Sans MT' font_size=11pt just=left verticalalign=middle;

  style caption/background=transparent cellheight=24pt;

  style pageno/font_face='Gill Sans MT' font_size=11pt;

  style byline/font_face='Gill Sans MT' font_size=11pt;

  style table/font_face='Gill Sans MT' font_size=11pt ;

  style header/verticalalign=middle cellheight=24pt bordertopwidth=1pt borderbottomwidth=1pt;

  style rowheader/verticalalign=top borderbottomwidth=0pt bordertopwidth=0pt;

  style parskip/cellheight=21pt background=transparent just=left;

  end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Proc tabulate and proc report

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

ods tagsets.excelxp_mod file="&desktop.xmltest.xml" style=excel_xml_test

                     options(embedded_titles='yes' embedded_footnotes='yes' suppress_bylines='yes'             

                             skip_space='0, 0, 1, 0, 0' row_heights='21, , , , , , 21' 

                             sheet_label=' ' sheet_interval='bygroup'

                             width_fudge='0.6');

options pageno=1;

proc report nofs headline data=sales;

  column (salesman year county sales, industry, product);

  define salesman/ group noprint;

  define year/group ' ' left;

  define county/group ' ';

  define industry/across ' ';

  define product/across ' ';

  define sales/style=[TAGATTR='format:#,##0'] ' ';

  compute after/style=[bordertopwidth=1pt];

    line ' ' ;

  endcomp;

  compute before _page_;

    line @1 salesman $20.;

  endcomp;

  compute before year/style=[font_size=3pt cellheight=3pt];  *<<< BTW, defining row height here doesn't work.;

    line ' ';

  endcomp;

  by salesman;

  footnote 'Footnote line 1';

  footnote2 'Footnote line 2';

run;

proc tabulate noseps data=sales f=comma8. style=[TAGATTR='format:#,##0'];

  class year salesman county product industry; var sales mileage;

  table salesman=' ', year=' '*county=' ', sales*sum=' '*Industry=' '*product=' '

       /indent=2 rts=16;

   title2'Salesman performance trends';

  footnote 'Footnote line 1';

  footnote2 'Footnote line 2';

  by salesman;

run;

ods tagsets.ExcelXP_mod close;

[Cynthia_sas · Accepted solution]

Hi:

  In my experience, Excel doesn't always respect the break grid line instructions. Just like Excel is somewhat erratic about using the HEIGHT= style attribute. I most often can only change my row heights in Excel by using the ExcelXP suboptions for height -- and not through the SAS style attributes.

  I notice a few things about your code. Generally, options like NOSEPS and RTS for TABULATE and HEADLINE, HEADSKIP for REPORT are LISTING only options. They are ignored by ODS destinations like HTML, RTF, PDF and TAGSETS.EXCELXP. LISTING-only options are respected only in the LISTING output. And your system options like orientation, pageno, number, date, etc are honored by HTML, RTF and PDF, but ORIENTATION, for example needs to be set using ExcelXP suboptions and page numbering can also be impacted by suboptions, too. I'm not sure why you have INDENT=, INDENT= with a number is only honored by the LISTING destination. INDENT=0 is used by ODS to suppress blanked out values in the row header area (http://support.sas.com/kb/35/204.html and http://support.sas.com/kb/4/438.html).

  PROC REPORT and PROC TABULATE are different procedures. PROC REPORT has a way to write extra code using a LINE statement in a COMPUTE block. PROC TABULATE does not have this capability. That is exactly the reason why folks use PROC REPORT, because they want that kind of control. But, with the right style, in the right destination, you can get a line under a table using TABULATE. See attached code. With the JOURNAL style, both REPORT and TABULATE will put a line at the end of a table without using a COMPUTE block.

  However, open the ExcelXP output file with Excel and you will see how the JOURNAL style attributes used so nicely by PDF are basically ignored by Excel. That's why you need a special style with Excel, special style elements and a COMPUTE block in PROC REPORT to do what you want (get a line at the bottom of the table. If the cosmetic lines are more important than what procedure you use, then I think you'll have to stick with PROC REPORT. If that line at the bottom of the table isn't critical, then the TABULATE syntax might be simpler.

cynthia

** code to try;

options nodate nonumber; 

ods pdf file='c:\temp\tab_journal.pdf' style=journal notoc;

ods tagsets.excelxp file='c:\temp\tab_journal.xml' style=journal;

  title;

  footnote 'Footnote line 1';

  footnote2 'Footnote line 2';

proc report data=sashelp.prdsale nowd spanrows;

title '1) PROC REPORT';

  column country prodtype product (region, division,actual);

  define country / group ' ';

  define prodtype / group' ';

  define product / group ' ';

  define region / across ' ';

  define division / across ' ';

  define actual / sum ' ' f=comma10.;

run;

proc tabulate data=sashelp.prdsale f=comma10.;

  title '2) PROC TABULATE';

  class country region division prodtype product;

  var actual;

  table country=' '*prodtype=' '*product=' ',

        sum=' '*actual='  '*region=' '*division=' ' ;

  run;

ods _all_ close;

title; footnote;

[AdrianGriffin]

Cynthia:

Thanks for your quick response. BTW, the noseps, indent=, etc, were left over from when I wrote the code for old-school monospace listing output.

--Adrian