Adding a work sheet to an excel file in unix using SP/ODS

[deleted_user]

Hi folks
is there anyway I can add worksheet using Stored process in unix to an excel file residing in unix. In other words can I create an excel file using ods first and then be able add a new work sheet at later time without recreating the file. I read some posting regarding this but most of them talk about excel in windows only. Any ideas?

Thanks in advance

Suren

[Cynthia_sas]

Hi,
Generally, folks use the SAS Add-in for Microsoft Office to be able to use Excel and 1) open SAS data files and data sources into Worksheets or 2) populate a worksheet with SP results or 3) run SAS analytical tasks on SAS data files or data sources. In this case, it doesn't matter where your data files physically reside or where your SP server is, as long as the folks who are using Excel have the right access privileges to the data and SPs.

So, I wonder what's going to happen to your Excel files on Unix once you create them? Presumably you're either going to open them FROM Windows with Excel OR you're going to open them on UNIX with some other software that reads Excel format files.

If you're going to open the Excel files with Windows, even though they reside on UNIX, then why not use the SAS Add-in for Microsoft Office? In this way, you have the full functionality of Excel with the added power of SAS to access data and/or run SPs.

If you're going to open the Excel format files on UNIX with some other software, then ... I'm not sure what client apps you would be using to execute your SP. What you want to do is really dependent on the client app that would be rendering the SP results.

The other challenge of file creation when you add SP into the mix is that with %stpbegin/%stpend you do NOT have any control over naming of the output file because the client application that runs the SP (Word or Excel or EG or PPT) is supposed to know how to render the results of the output. Right now, SpreadsheetML (TAGSETS.EXCELXP ) output is not accepted by the SAS Add-in for Microsoft Office. You can only execute SPs that return TAGSETS.EXCELXP output from within the Information Delivery Portal or your own custom invocation of a SP (using the Stored Process Web Application or the Stored Process API).

In this previous post,
http://support.sas.com/forums/thread.jspa?messageID=6617᧙
you indicated that you were using the Information Delivery Portal and %stpbegin method to return ExcelXP output. When you use the Portal, you do not have a fixed file name for your streamed output (unless you are publishing packages and NOT using streaming results) -- so there is really no way to do an append using a SP since %stpbegin does not let you set a file name. And, besides, with TAGSETS.EXCELXP, you cannot append to an existing SpreadsheetML XML file, however, because the XML specification for SpreadsheetML requires 1 and only 1 tag in the file.

There might be some way with a custom front end application using C++ or VB that you could grab the SP results and open the original Excel file behind the scenes and then append the streamed retults into a new worksheet -- but now you're essentially writing the same kind of application interface that you get out of the SAS Add-in for Microsoft Office.

Imagine this scenario -- every month, somebody has to add a new worksheet to the yearly workbook with the previous month's report/results. On Jan 31 or Feb 1, using the SAS Add-in for Excel, you run the SP in an empty workbook and make a worksheet for January. Then you save the Workbook. On Feb 28 or March 1, using the SAS Add-in for Excel, you open the yearly workbook, click to start a new worksheet and run the SP to make a worksheet for Feb. You keep doing this every month until your yearly workbook is complete. When you use this method, you don't really need to worry about using TAGSETS.EXCELXP and you can just return HTML results to Excel. Since the end user is in control of when the SPs populate the worksheets or add new worksheets, they can also control the name of the worksheet.

Anyway, just some more food for thought.
cynthia

[deleted_user]

Hi Cynthia
thanks for the reply and I am always amazed with the knowledge you have and the quick responses. As much as I would like to use MS add-ins , there is always a gate keeper called SAS Administrator, who would always limit power of SAS by enforcing some controls around the SAS server:-)

I developed an excel sheet with 20 sheets of report and charts for which the data get updated on demand from the sas server using VBA and SAS IOM. So it's more like updating the data from the sas server to an excel template. Due to some data security , the Administrator want me to set up the report in the server so that , it can be emailed from the server directly . So the quick fix for me is to put the excel template in the sas server and when the request comes just populate the data and send the excel file automatically.(If it's possible).. Ofcourse I was assuming I can onerwrite the data sheet only in the excel file.( So the links within in the excel sheet will update the charts and the tables). Any Ideas?

cheers
Suren

[Cynthia_sas]

Sounds like your SAS Administrator is a tough gatekeeper.

I don't have that much experience with Excel templates (.xlt) files -- the ONLY thing I've done is make a simple Excel template with colors and fonts in specific cells and then populate the XLT file from a separate CSV file. But as I remember, I had to open the XLT file and then do a manual save as an XLS file or something like that so it wasn't entirely automatic.

If you need to populate files with specific names on the server, I probably would not do that as a stored process -- because the SP limits your ability to name the files and assumes that there will be a person on the other end of the SP who is controlling things via the client interface.

This almost sounds like a regular old "batch" SAS job to me -- one that you'd schedule perhaps with DI Studio or through your scheduler on the server system. Sadly, I have NO experience with VBA or VBScript. Tech Support might be a better place to get these questions answered. You're still going to have issues because you can't append to an ODS-created ExcelXP file. Again, Tech Support might be able to help you with a process flow that will work for your restrictions.

cynthia

[deleted_user]

Thanks Cynthia
I will try the tech support.

cheers
Suren

[deleted_user]

I think there is a problem with using VBA or VBScript in this process anyway Cynthia.

While it is conceivable that you can open an Excel Workbook and add a sheet with macros, which you populate using data from a SAS dataset, this is usually an element of DDE. To do this you need to open an instance of Excel to open the workbook, and since the server is Unix, that seems to be impossible.

If someone has made this approach work, I'd like to know where I strayed off the path. The only alternative I can think of is using the Office on the client machine, but if the data and the processing are on the server, then this doesn't seem to be possible either.

Kind regards

David

[Cynthia_sas]

I was also thinking of having to use Office on the client machine and VB on the client machine to reach through to the file-- perhaps using OLE-DB instead of DDE??? But if it is a "true, binary" Excel file, then you are right that this probably wouldn't work because of having to open an instance of Excel. If the storage location on the Unix server was physically mapped to the Windows machine that was running Excel -- then I guess I thought it might work.

Since I responded, I've been thinking of that there might be a trick where you have a series of HTML files sitting in a subdirectory and, if you have the right kind of XML description of the linked HTML files, you can get Excel to open the set of HTML files and each HTML file becomes a worksheet -- since HTML files and XML files are just ASCII text files, you could pop into the UNIX directory and add an HTML file to the directory and then edit the XML file to include the newly added file -- all with DATA step programming -- but even if you could make it work in batch mode, it doesn't answer the initial posted question which was how or whether you could do this in a stored process. And, again, I come back to the fact that when you use %stpbegin/%stpend, you do not have the ability to control the name of the streamed results. So then, while you might still point your SP at the hardcoded files sitting on the server, it seems that we're back to why use a stored process to do what a batch job can already do well.

Thanks for pointing out the fly in my VBScript ointment!

cynthia