ExcelXP: Formatting Titles, Getting Formatted Values into tabs

[deleted_user]

First question:
I have some titles that I would like to get put into separate cells sort of like a form, say.
|Name:| |______| |Date:| |______| |Something Else:| |______|

I'm sure you get the idea. I've tried various things like , '09'x, etc. with no luck.

I at least set the options nocenter and that me somewhare, sort of.

Is there a way to split titles?

Second question.

I am writing out spreadsheets by month. Right now, I've extracting a month at a time and setting the sheetname to 'Jan', say.

Is there a way to use a BY statement, Format the date to MONYY5., say and use the formatted name for the sheetname.

Merci, in advance.

[Cynthia_sas]

Hi:
The standard technique that you can use with titles such as:[pre]
title j=l "Left Side" j=c "Middle"; [/pre] is not respected by either MSOFFICE2K or EXCELXP (They put every string on a new line). However ODS HTML -will- put each string into a separate cell at the top of the file when you open it into Excel.

With Proc Report, you could simulate the equivalent of a title, but having spanning headers above columns.[pre]
column ('Name' name age)('Date' weight) ('Something Else' height);
[/pre] But, The spanning headers go into the boundary of the table when opened in Excel, which may be OK or may not be OK.

It seems to me that you want something like a fixed header inside Excel. That does not necessarily have to come from the SAS title. Either the MSOFFICE2K tagset or the EXCELXP tagset can have information put into the header area of the spreadsheet, via a tagset change. (My NESUG paper last year was a case study of doing just this (and a bunch of other changes) for MSOFFICE2k). You can find it here:
[pre]
http://support.sas.com/rnd/papers/regional05/ODSCaseStudy.zip
[/pre]
Since you asked about multiple worksheets, you might not be satisfied with an MSOFFICE2K solution. Ultimately, to me, it looks like you want to design a form and have specific strings (from SAS) put into specific cells (in Excel) -- there have been a few long discussions here on the forum about how that is not possible right now, except with DDE. Other people in my Advanced ODS classes have created their spreadsheet and used a VB macro to perform the last bit of formatting. (FWIW, I have also helped somebody write a custom tagset to create the kind of Form Definition File in XML (.FDF file) that is used for PDF forms.)

As for your second question, I believe that the ExcelXP tagset automatically numbers the worksheets that it creates. Eric will have to chime in here. I think you can change the tagset to grab the BYLINE info and use that for the worksheet label. I think that question has come up before -- either here in the forum or with a student over e-mail. What I can't remember is whether #BYVAR/#BYVAL are going to be available in 9.2 for worksheet labelling -without- a tagset change or whether that's just a figment of my imagination.
cynthia

[OS2Rules]

I've had exactly the same problem and managed a solution.

You need to imbed some XML code into the titles so that they will be split into seperate columns.

When ExcelXP creates the XML code, have a look at it with an editor and you can see how it creates the columns of the excel sheets. Somewhere in there is a section that describes each column - you titles will be placed just after this code and you can use the column definitons to specify which columns the title is in.

For example, I use:

title1 'Title One'
''
'Title Two'
''
'Title Three';

to create titles in cells 1 (defauled), 3 and 5 of the sheet.

Hope this does what you want.

[deleted_user]

My thanks to both of you. Sorry about the tardiness of response.

In the immediate future, I guess I'm going to try Dan's suggestion, if for no other reason than it helps me gain some insight into how all this this stuff (html, xml, etc.) goes together. Which, I suspect, will be useful when it comes to modifying the tagset, which seems to be the long-term solution.

Haven't had the opportunity to read your paper, yet, Cynthia, but looks like that'll be on my short list - maybe something to take home for some holiday reading - now if I only had some rolling waves and sandy beaches nearby ...

I have more, but let me formulate my questions first.