topic Re: Image overlapping data table. in SAS Enterprise Guide

Image overlapping data table.

SAPPER — Tue, 14 Dec 2010 21:40:38 GMT

Hi All,
I am trying to export a logo to the excel sheet using the msoffice2k ODS destination and I use the following code to do the export.The code exports the image but it overlaps with a couple of rows of data.
Are thee any options in the msoffice2k tagset where i can specify it to start the data after the image?
I tried looking into the doc="help" option but couldnt figure out anything straightforward that would fix things.

ods escapechar="~";
ods tagsets.msoffice2k file="G:\worksheet_msoffice2k_1.xls" style=default
options( doc="help"
);

proc print data=sashelp.prdsale(obs=50);
title j=c " ~S={preimage='G:\sample_reports\logo.gif'} ";
run;

ods tagsets.msoffice2k close;

Thanks
Sapper

Re: Image overlapping data table.

Cynthia_sas — Tue, 14 Dec 2010 22:49:04 GMT

Hi:
When you use TAGSETS.MSOFFICE2K, you are not creating a true, binary Excel (.XLS) file. You are actually creating an HTML file that Excel knows how to open and render. Naming the file with the ".XLS" extension only "fools" the Windows registry into launching Excel when you double click on the file name.

So, for example, if you open G:\worksheet_msoffice2k_1.xls with Notepad, or any Text Editor, you should see HTML tags in the file. And if you search for the name of your image file, you should soon find an <IMG> tag that pointed to the GIF file.

Personally, I am never happy with the look of logos when I have Excel open and render an HTML file. Excel seems to want to "float" the image over the table. This never looks the way I want. The behavior you observe seems to be inherent in how Excel treats HTML <IMG> tags.

You might want to work with Tech Support to see whether they have any workarounds for this behavior.

cynthia

Re: Image overlapping data table.

SAPPER — Wed, 15 Dec 2010 00:04:29 GMT

hi Cynthia,
Thanks for the reply.
Is there any other ODS destinations that would support exporting images perfectly to excel other than msoffice2k?

The problem with using other Html ODS tagset destinations they donot create a true or pesudo ".xls" sheet as an output and we at the site require that the output file be truly .xls and not .html or .xml
So I chose the msoffice2k ODS tagset.

Can anyone let me know if there is any other way of exporting a logo onto excel and creating a true or pseudo excel (.xls) sheet?

Thanks
Sapper

Re: Image overlapping data table.

twocanbazza — Wed, 15 Dec 2010 00:57:47 GMT

Have a look at

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

it talks about msoffice2k_x might do what you need....

Re: Image overlapping data table.

Cynthia_sas — Wed, 15 Dec 2010 01:50:39 GMT

As cool as the new TAGSETS.MSOFFICE2K_X is, it is still producing HTML -- so if you follow the instructions and download the template code and run the template code, if you look down inside the file that's created, it contains:
[pre]
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<meta name="Generator" content="SAS Software Version 9.2, see www.sas.com">
<meta http-equiv="Content-type" content="text/html; charset=windows-1252">
[/pre]

....HTML tags, even when the file extension is .XLS -- so if the desire is to have a "true, binary" .XLS proprietary file, this tagset still creates HTML.

I have had folks write their output to a directory location and then write a VB script to go out and open the HTML files in that directory with Excel and explicitly resave them as Excel proprietary format files (either XLS or XLSX).

The only mechanisms I know of in SAS to create proprietary Excel files from SAS data are to:
1) use PROC EXPORT (with SAS/ACCESS to PC File Formats)
2) use the SAS Excel LIBNAME Engine (needs SAS/Access to PC File Formats)
3) use the SAS Add-in to Microsoft Office (requires the SAS Platform for Business Analytics)

Otherwise, when you use ODS MSOFFICE2K or ODS TAGSETS.MSOFFICE2K_X -- you are still creating Microsoft HTML compliant files that Excel knows how to open and render. When you use ODS TAGSETS.EXCELXP, you are creating Microsoft XML Spreadsheet 2003 compliant files that Excel knows how to open and render.

cynthia

Re: Image overlapping data table.

twocanbazza — Wed, 15 Dec 2010 02:00:56 GMT

Fully understand cynthia, but I think the issues is also OP wants an image in the excel file which can't be done by ExcelXP, or export or any other easy way

Re: Image overlapping data table.

SAPPER — Wed, 15 Dec 2010 03:41:25 GMT

Thanks cynthia and twocanbazza for the replies.
I have created xml files with excelXP that were directly opened with excel but then they included the requirement that the excel sheet should contain the logo in it.
So had to switch to msoffice2k from excel XP.

We donot have SAS Access to PC files installed on site and i don't see it to be installed in the near future.

I have found another ODS tagset "The tableeditor tagset ".
It says that I can apply the logos in the documentation

http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html

If you go down the page and look in the "Exporting to Excel" section there seems to be an option called "excel_save_file" .

Does this mean that it is saving the created html file in a excel(.xls) format just like the custom VB script that cynthia was talking ??

If anyone has used this tagset to create excel sheets please clarify my question on the "Excel_Save_file" option.
I have never used this tagset and will probabaly try tommorow .

Thanks
Sapper

Re: Image overlapping data table.

Cynthia_sas — Wed, 15 Dec 2010 03:44:20 GMT

From what was said by the OP (underline mine):

"we at the site require that the output file be truly .xls and not .html or .xml
So I chose the msoffice2k ODS tagset."

The OP chose MSOFFICE2K without understanding that it was HTML "under the covers" -- so if HTML turns out to be OK, then, you're right, the new MSOFFICE2K_X tagset template may offer the ability to insert a "non-floating" logo into the HTML file. But it will still be HTML output from either MSOFFICE2K or TAGSETS.MSOFFICE2K_X.

If the client is not willing to have an HTML file instead of proprietary XLS, then other solutions must be found (OLE, ODBC, DDE, populate an XLT with a CSV file, etc, etc.)

cynthia

Re: Image overlapping data table.

SAPPER — Wed, 15 Dec 2010 03:53:59 GMT

HI cynthia,
If you Can point me towards the "other" solutions that you were talking in your reply, it would be really helpful to check the alternatives to MSOffice2k / 2K_X tagsets.

Thanks
Sapper

Re: Image overlapping data table.

Cynthia_sas — Wed, 15 Dec 2010 04:09:30 GMT

Hi:
TAGSETS.TABLEEDITOR uses HTML with embedded JavaScript. If you can load your browser with HTML files that have embedded JavaScript to do the creation of an HTML file, this solution may work. However, I believe that the HTML file must load into the browser first before the JavaScript "Export/Save" button becomes active.
Information on TAGSETS.TABLEEDITOR is here:
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html
and
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html#exporte (see the suboptions EXCEL_SAVE_FILE and UPDATE_TARGET starting at this location in the description of TAGSETS.TABLEEDITOR)

As for the other methods -- OLE-DB, ODBC, DDE, creating Microsoft XLT files -- those are all Microsoft topics. To the extent that you can use OLE-DB, ODBC and/or DDE with SAS -- you are basically sending Microsoft commands to Excel using a "conversation" between two applications. If you are using SAS in a client/server scenario -- such as EG on Windows and SAS on Unix, then you may not be able to use DDE, etc, because I believe both apps have to be talking to each other on the same system for the "conversation" to take place.

There are some user group papers on DDE, ODBC, OLE-DB, etc -- probably more DDE examples than anything else out there.
(If you do a Google search on:
Koen Vyverman SAS DDE
or
Leroy Bessler SAS DDE
you should find some good papers.)

It looks to me like TAGSETS.MSOFFICE2K_X will allow you to load an Excel macro when the file is opened or closed -- this may be another alternative -- if your clients allow the use of Excel macros -- it might be a simple Excel macro as described here:
http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#Macro (of course that means the Excel macro would also have to be written and resident on the user's system).

Re: Image overlapping data table.

twocanbazza — Wed, 15 Dec 2010 18:44:02 GMT

SAPPER.

We are having the same issues here, we have been using the ExcelXP tagset and now we want our logo in the supplied output.

Other issues with the 2k and ExcelXP tagset is as Cynthia rightly pointed out is that the output is not true excel (ie Markup, HTML or XML) which produces large files when there is alot of data.

I'll let you know how we get one, or if you beat us to it let me know how you go.

I think think there is going to be a simple answer to this one.

Regards

Barry

Re: Image overlapping data table.

Cynthia_sas — Wed, 15 Dec 2010 19:44:55 GMT

Hi:
I don't write VB Script code. But I have had students tell me that a quick way to get "true" Excel files is to write a VB Script to go out, open the HTML or XML file and then do a SAVE AS--> XLS (or whatever Excel version you want) -- before the file was made available for general consumption.

cynthia

Re: Image overlapping data table.

twocanbazza — Wed, 15 Dec 2010 19:50:25 GMT

Thanks Cynthia.

For the VB script to work does excel have to be on the server running the code?

Barry

Re: Image overlapping data table.

Cynthia_sas — Wed, 15 Dec 2010 21:50:09 GMT

Hi:
I'm not really sure. The folks who have explained it to me were writing their ODS output to a shared Windows directory, like a Q: drive and then running the VB Script on everything in that directory by opening the file with Excel and doing a file Save AS --> XLS -- so I would imagine that the key is that the VB Script and Excel have to be somewhere in the same vicinity or the files to be saved have to be accessible to Excel, whereEVER it is. Nobody said anything about the mechanics fo the VB Script and Excel, server-wise.

cynthia

Re: Image overlapping data table.

twocanbazza — Wed, 15 Dec 2010 22:12:13 GMT

Thanks

Re: Image overlapping data table.

SAPPER — Thu, 16 Dec 2010 21:59:18 GMT

Hi Everyone.
SAS Tech support came up with the Suggestion to use MSOFFICE2K_X tagset instead of MSOFFICE2K.
This was the last option on the plate..

The tagset fixed the problem and there are certain options in the tagset which can be used to adjust the image and the table placement in Excel.
I havent fully tested all the other things in the tagset (Like exporting multiple worksheets and defining multiple Worksheet names) but the basic task of exporting the table along with the logo works with this without overlapping the image on the table.

Here are some of the options that i ve used with it.
(
doc="help"
Margin="0.5in 0.5in 0.5in 0.5in"
Orientation="landscape"
draftquality="Yes"
fittopage="Yes"
frozen_headers="3"
frozen_rowheaders="3"
image_path="C:\logo.JPG"

image_height="52"
image_width="183"

sheet_name="MY_Sheet"
)

The doc="help" option puts up a lot of information on the SAS log and you can look at it to figure out what each option does in detail.

Hope this helps others.

Thanks
SAPPER.