BookmarkSubscribeRSS Feed
jim_snider
Calcite | Level 5
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.
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
JackHamilton
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
jim_snider
Calcite | Level 5
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.
deleted_user
Not applicable
see below.... Message was edited by: hallamus
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 1053 views
  • 0 likes
  • 5 in conversation