topic Re: Proc template with ODS Tagsets ExcelXP Options in ODS and Base Reporting

Proc template with ODS Tagsets ExcelXP Options

Ujjawal — Sat, 16 Apr 2016 19:10:26 GMT

I am trying to spice up the table format with PROC TEMPLATE.

proc template;
define style mystyle;
/* Body */
 class body / 
 backgroundcolor = white
 color = black ;
 /* Tables */
 class table /
 frame = box
 rules = all
 borderwidth = 1px
 borderstyle = solid
 bordercolor = #5D5D5D
 borderspacing = 0
 bordercollapse = collapse
 cellpadding = 5px;
 class data, header, rowheader /
 fontfamily = '"Lucida Grande", Arial, sans-serif'
 fontsize = 11pt
 backgroundcolor = #FFF
 color = black;
 class header, rowheader /
 fontweight = medium
 textalign = left
 fontsize = 12pt
 backgroundcolor = #1F1C7C
 color = #FFF;
 /* System Title and Footers */
 class systemtitle, systemfooter /
 fontfamily = '"Lucida Grande", Arial, sans-serif';
 /* Page number and date (for printer) */
 class pageno, bodydate /
 fontfamily = '"Lucida Grande", Arial, sans-serif' ;
end;
run;

ods html style=mystyle file = 'C:\Users\Ujjawal\Downloads\temp.htm';
proc print data=sashelp.class noobs;
run;
ods _all_ close;

The above code works fine if i use ODS HTML. But i need to create multi-sheet excel file. So i am using ODS TAGSETS EXCELXP. It does not work with ODS TAGSETS EXCELXP.

ods tagsets.excelxp file='C:\Users\Ujjawal\Documents\multii.xls' style=mystyle
options(sheet_name='Summary' skip_space='1,0,0,0,1' EMBEDDED_TITLES='yes' sheet_interval='none' suppress_bylines='no');
proc print data=sashelp.class noobs;
run;
ods tagsets.excelxp close;

What i am doing using PROC TEMPLATE - Making background color of header BLUE (#1F1C7C) and background color of border LIGHT BLACK (#5D5D5D) .

Re: Proc template with ODS Tagsets ExcelXP Options

amadhavi41 — Sun, 17 Apr 2016 00:51:19 GMT

What is the issue/error you are getting?

Re: Proc template with ODS Tagsets ExcelXP Options

Reeza — Sun, 17 Apr 2016 02:06:01 GMT

I would grab a template I know that works and change that to see if I could get it working.

I know proc report allows you to customize formatting for tagsets so you do have work around options.

Re: Proc template with ODS Tagsets ExcelXP Options

Cynthia_sas — Sun, 17 Apr 2016 16:28:24 GMT

Hi: I would expect you to get an error message when you try to open the Excel file -- I believe you have your quoting wrong in the FONTFAMILY attribute.
When I try a version of your template with this:
fontfamily = '"Times New Roman", Arial, sans-serif';
(because I don't have Lucida Grande on my system), I get an error message from Excel.

But, if I have this:
fontfamily = 'Times New Roman, Arial, sans-serif' ;
then I don't have an error message from Excel.

Also, aside from the sheet_interval='none' issue, typically, I find border colors, border styles and border widths generally ignored by Excel, So I believe you can simplify your template.

Also, if you plan to share your template with other people, then you will generally need to write the template to an item store on a shared drive or server where other folks can get to it.

cynthia

Also, with sheet_interval='none' you are telling TAGSETS.EXCELXP that you do NOT want a multi-sheet workbook, but that you want ALL your procedure output in one worksheet.