msoffice2k_x skip space option

[haydn89]

Hello all,

I'm looking for some help with manipulating the msoffice2k_x tagset.

Firstly some background. I am trying to create a template using sas that looks as close as possible to the template style I use at work.

I am using the msoffice2k_x tagset because it exports the results into excel, and I can import and save my workplace logo in the title. I am led to believe xml files can't do this, ruling out the use of the excelxp tagset.

I have been experimenting using some sashelp data sets. Unfortunatley, when I have mulitple titles in my tabulate proceduce, there is a row space between each title. You can see a copy of the code used attached in chp2_1.sas and the actual output in custom.xls.

I would like to get rid of the row space between title1 and title2. I am led to believe that there is such an option in the excelxp tagset called skip_space, but I don't know of anything equivalent for the msoffice2k_x tagset. I can't use the excelxp tagset because I want to be able to import and save the departments logo at the top.

I read somewhere that you can create options in the msoffice2k_x tagset that mimics the same options as the excelxp tagset. Does anyone know how to do this or if there is a better way to get rid of the row space between title1 and title2?

I apologise in advance if I have not provided enough information. I am very new to the SAS communities forum.

Kind regards,

Haydn.

[Cynthia_sas · Accepted solution]

Hi...some thoughts ...

You said

"I am using the msoffice2k_x tagset because it exports the results into excel, and I can import and save my workplace logo in the title. I am led to believe xml files can't do this, ruling out the use of the excelxp tagset" And there are several points to clarify in this statement:

1)  It is by Microsoft design that the XML specification they wrote for Spreadsheet Markup Language XML does not support graphs or logos. This is the type of XML that ODS creates when you use TAGSETS.EXCELXP. So the fact that you cannot use logos with TAGSETS.EXCELXP is something that SAS has no control over. That is why you cannot use TAGSETS.EXCELXP and why you have to use an HTML-based destination, like ODS MSOFFICE2K or ODS TAGSETS.MSOFFICE2K_X.

2) You are not using the ODS TAGSETS.MSOFFICE2K_X tagset to truly "export" your results into Excel. When you use ODS TAGSETS.MSOFFICE2K_X tagset, you are creating an HTML file that can be opened with Excel. Excel has been able to open HTML files since Office 97. Then, with Office 2000, Microsoft invented their own "flavor" of HTML -- that is what is created with ODS MSOFFICE2K and ODS MSOFFICE2K_X. Even though you name the file with a .XLS extension, you are doing this to "fool" the Windows registry to automatically open the file with Excel when you double click. Otherwise, you would have to go into Excel and do a File --> Open and point to the HTML file to open it. You can prove to yourself that you are not creating a true, binary XLS file, by looking at your result file with Notepad and you will see HTML tags inside the file.

  Generally, all ODS "TAGSET" destinations have the ability for you to specify options(doc='Help') so you can see what sub-options are supported by the tagset destination. It doesn't look to me like ODS MSOFFICE2K_X has the equivalent of the SKIP_SPACE suboption.

  You also asked "I read somewhere that you can create options in the msoffice2k_x tagset that mimics the same options as the excelxp tagset. Does anyone know how to do this..." The way you "create options or sub-options for TAGSETS.MSOFFICE2K_X would be to use PROC TEMPLATE to create a new tagset template, starting from TAGSETS.MSOFFICE2K_X as your "base" or "starting" template. You would need to learn PROC TEMPLATE syntax for TAGSET templates. It would be similar to, but entirely different syntax than what you did for your STYLE template. Here's a paper with an overview of the differences between 4 of the types of templates that you can create with PROC TEMPLATE (http://support.sas.com/resources/papers/proceedings09/227-2009.pdf).  However, a tagset template is about 10 times more complicated than a style template and I'd check with Tech Support first, because if what you want to do is NOT possible, then it wouldn't be worth even starting the attempt to write your own sub-options because you'd have to know 1) whether there was HTML that Excel would interpret the way you want and then 2) find the event that wrote the TITLE tags into the HTML file and then 3) alter the event to write different tags. And the big IF is -- does Excel even have a way for you to skip the space.

  One thing I was thinking was something a bit simpler, using a feature of ODS ESCAPECHAR. Basically, I changed your TITLE statement so instead of 2 TITLE statements, you just had 1 and you put your own "line feed" between the 2 lines:

proc print data = sashelp.class(obs=10) noobs;

var name / style(header)={just=l} style(data)={just=l};

var sex age height weight / style(data)={just=r} style(header)={just=r};

title1 'Test title^nTest 2nd Line';

run;

I used a really tiny, tiny picture of Kermit as my logo. You can see the results with an "extra" row 4 from running your original title statements and then the second output from using my changed title statement. Of course, you could still pursue the idea of modifying the tagset template. If you look at the underlying HTML created by TAGSETS.MSOFFICE2K_X, for your ORIGINAL code, you will see this HTML:

<body class="Body" style=" text-align: center;">

<h1 class="systemtitle" style=" text-align: center;">Test title</h1>

<h1 class="systemtitle2" style=" text-align: center;">Title2</h1>

created by the original code (2 H1 tags) versus this HTML created by my technique:

<h1 class="systemtitle" style=" text-align: center;">

Test title<br/>

Test 2nd Line</h1>

So, it is something inherent in how Excel treats the 2 separate H1 tags that is putting the extra line in the Excel file. As you can see in the generated HTML there are only H1 tags being written by default. So changing the tagset template will be challenging and you should consult with Tech Support before you try it.

When you use the declared ESCAPECHAR (^) with n or with {newline}, it causes a simple line feed or break to be generated.

Depending on what you want to do, using ESCAPECHAR and the TITLE statement may be faster and easier than changing the TAGSET template.

cynthia

[haydn89]

Thank you very much Cynthia, your suggestion worked a treat! It will certainly suffice in the short term.

Kind regards,

Haydn.

[Reeza]

Cynthia's suggestion is better than this one, but I thought I'd mention it.

1. Create XML file with EXCELXP

2. Convert to XLSX via VBS macro

3. Add Logo in via DDE or another VBS Script.