The SAS Output Delivery System and reporting techniques

ODS HTML Proc Report output to Excel

Reply
Occasional Contributor
Posts: 8

ODS HTML Proc Report output to Excel

My users increasingly favor output to Excel rather than pdf. So, I've been
converting my reports from ods pdf to ods html, with output to an .xls file.
I have 4 unresolved problems that cause me to have to manually clean up the output.
1. I'd like to output multiple reports to the same workbook, but different sheets.
2. I'd like to tell Excel to "merge cells" so that my title lines and footnotes don't wrap inside the first column.
3. I use the parameter - htmlstyle="mso-number-format:\@ on the style(column) statement to avoid having large numeric values, such as account numbers, be in exponential format. But, I'd like to limit this to specific column rather than all numerics in the report.
4. After generating my report, I have to save it as an Excel Workbook, since the default is Web Page. I'd like to have the default be an Excel Workbook.

Any ideas? thanks.
Super Contributor
Super Contributor
Posts: 3,174

Re: ODS HTML Proc Report output to Excel

Posted in reply to jim_snider
Check out the SAS support website for info resources on TAGSETS.EXCELXP. Also, here is a particular link on an updated version of the particular tagset.

Scott Barry
SBBWorks, Inc.

Usage Note 32394: Installing and Storing Updated Tagsets for ODS MARKUP
http://support.sas.com/kb/32/394.html

ODS MARKUP Resources
http://support.sas.com/rnd/base/ods/odsmarkup/index.html
Frequent Contributor
Posts: 102

Re: ODS HTML Proc Report output to Excel

Posted in reply to jim_snider
Use the ExcelXP tagset, not ODS HTML.

The MSOffice2K tagset is another possibility, but I don't have much experience with it.

Try the tagset to see if it does what you want with merged cells.

You can assign a style to a number that gets passed through to Excel, such as (in PROC REPORT)

define Mbsh_Count / display style={tagattr="format:#,##0"};

This creates an XML file, not a native Excel workbook, but if you give it an extension of .xls it will open as wanted in recent versions of Excel.
SAS Super FREQ
Posts: 8,868

Re: ODS HTML Proc Report output to Excel

Posted in reply to jim_snider
Hi:
My comments under your questions.

1. I'd like to output multiple reports to the same workbook, but different sheets.
--> This -IS- possible with ODS HTML, but is sort of a pain to manage. The preferred method to create multiple sheets in one Workbook is to switch to ODS TAGSETS.EXCELXP instead of ODS HTML. If you MUST stick with ODS HTML then you have to follow the steps outlined in this SUGI paper with ODS HTML:
http://support.sas.com/rnd/base/ods/excel/multisheet_excel_post.ppt

However, TAGSETS.EXCELXP creates Microsoft Spreadsheet Markup Language XML and will create multiple worksheets automatically. The only "downside" is that SpreadsheetML does not support the insertion of graphic images (by Microsoft design).

2. I'd like to tell Excel to "merge cells" so that my title lines and footnotes don't wrap inside the first column.
--> Try ODS MSOFFICE2K (Microsoft-compliant "flavor" of HTML) or, again, try TAGSETS.EXCELXP (Microsoft-compliant "flavor" of XML) -- which actually puts your titles into either the header of the document OR embedded into the
worksheet(s).

3. I use the parameter - htmlstyle="mso-number-format:\@ on the style(column) statement to avoid having large numeric values, such as account numbers, be in exponential format. But, I'd like to limit this to specific column rather than all numerics in the report.
--> If you are using PROC PRINT, PROC REPORT or PROC TABULATE, then you can specify the HTMLSTYLE attribute on a DEFINE or VAR statement or crossed with a variable in a TABLE statement, so the M-soft format only affects one column. Also, with TAGSETS.EXCELXP, (if you decided to switch), you can still use mso-number-formats -- only you use them with the TAGATTR style attribute instead of the HTMLSTYLE attribute.



4. After generating my report, I have to save it as an Excel Workbook, since the default is Web Page. I'd like to have the default be an Excel Workbook.
--> Ah, this is a Microsoft "feature". It is Microsoft letting you know that IT knows that you tried to fool it.

ODS does not create a "true binary" .XLS format files -- it creates either CSV or HTML or XML "flavor" of files -- all of these 3 types of files are ASCII text files. No matter what file extension you give them, if you open the files you create in Notepad, you should see HTML tags or XML tags in the file.

You only "fool" the Windows registry by naming the extension with .XLS so Excel launches instead of a browser. Microsoft "knows" that you did not send it a "true" XLS file and that becomes apparent when you go to save. In fact, in Office 2007, Microsoft grumbles a bit when you try to -open- an HTML or XML file that you named with .XLS -- and Excel pops open an annoying window to which you MUST respond 'YES' in order to open the file.

The only workaround I've heard for the "save as" issue is to run a "batch" VB script that opens the file and does the SAVE AS .XLS for you.

cynthia
Occasional Contributor
Posts: 8

Re: ODS HTML Proc Report output to Excel

Posted in reply to Cynthia_sas
Several replies encouraged switching to tagsets.excelxp. however, since most of my reports include the corporate logo (.jpg file), it sounds like that's not feasible.
N/A
Posts: 0

Re: ODS HTML Proc Report output to Excel

Posted in reply to jim_snider
see below.... Message was edited by: hallamus
N/A
Posts: 0

Re: ODS HTML Proc Report output to Excel

Posted in reply to deleted_user
You could use DDE to save the file as an exel file (saving space) and also to add the imagine to anywhere in each tab or to the required tabs. Remove the loop and pick the tabs required using the [workbook.activate("SUMMARY")] function.

You will need to create a template excel file which contains the imagine(s).

Then use the following code....

%let logos=;
%let Report = ;
%let loop_num = ;

/* open up excel (standard DDE code from SAS) */

options noxsync noxwait noxmin;
filename excelchk dde 'excel|system' command;
%let excelstarted = %sysfunc(fopen(excelchk,S));
data _null_;
length fid rc start stop time 8;
fid=&excelstarted;
if fid le 0 then do;
rc=system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid=fopen('excelchk','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
run;
%let rc = %sysfunc(fclose(&excelstarted));
filename excelchk clear;

/* Connect to Excel */
filename cmds dde 'excel|system' command;

/* Open a specific file in excel */
data _null_;
file cmds;

/* set error handler off */
put '[Error(false)]';

Put "[open(""&logos\Logo Template.xls"",0,true)]";
put '[select("R1:R1")]'; /* select the rows containing the imagine */
Put '[COPY()]'; /* speaks for itself */

Put "[open(""&report\test.xls"",0,false)]"; /* open to overwrite Not READ ONLY*/
put '[workbook.activate("SUMMARY")]'; /* select tab name */
put '[select("R1:R1")]'; /* select place to past */
put '[INSERT(2)]'; /* inserts imagines above the selected */
Put '[COPY()]'; /* only required for other tabs */

/* loop all the remain tabs to add in the logos */
Do i=1 to &loop_num;
put '[ACTIVATE.NEXT("test.xls")]';
put '[select("R1:R1")]';
put '[INSERT(2)]';
Put '[COPY()]';
end;

/* select the summary sheet for saving purposes */
put '[workbook.activate("Summary")]';

put "[save.as(""&report\test.xls"",1)]"; /* 1 = save as normal excel */

put '[quit()]';

run;

I hope this helps. Message was edited by: hallamus
Ask a Question
Discussion stats
  • 6 replies
  • 294 views
  • 0 likes
  • 5 in conversation