01-31-2014 10:14 AM
I'm running a scheduled sas job on a sas unix server: I would like to produce an xlsx report, where datas are written on specific cells on an xlsx pre-colored template. I tried doing it with proc export dbms=xls, but I got a xls instead of an xlsx and only a plain export of datas (not in specific cells). Considering that it is a unix server, and that there are no other servers available (eg a windows pc files server), how can I do what I'm trying to do?
Thanks a lot
01-31-2014 04:16 PM
There are very specific rules for how to create Excel files when you use PROC EXPORT or the LIBNAME engine. Generally, you cannot write to specific cells unless you have defined named ranges in the existing spreadsheet, and even then, each method (EXPORT or the LIBNAME engine) may have different limitations. Given that there are also version differences in the code you would use, I would recommend working with Tech Support on this issue, the code you need could be impacted by the version of SAS you have, the bitness of SAS and your operating, where SAS lives and if the SAS server can access a Windows machine with Excel.
And, with a stored process, running on the BI Platform, that adds just another layer of complexity. If you use the SAS Add-in for Microsoft Office, then you would not really need PROC EXPORT at all. So it's worth working with Tech Support because they can figure out your configuration and whether you even need to use PROC EXPORT. For example, when you run a stored process using the SAS Add-in for Microsoft Office, you have the choice to put the stored process results in a specific sheet or in a specific location in a sheet as part of the interface for running the stored process.
01-31-2014 04:34 PM
Thank you for your answer, I can answer to your questions:
- I have prepared an xlsx template, where e.g. a range is named NamedRange
- SAS version is 9.3, running entirely on a unix machine
- the bitness of the unix machine is 64bit
- the SAS server cannot access any other machine (neither unix nor Windows), it just lives entirely on it's own 64bit unix machine
- the stored process I'd like to build is scheduled through SAS Management Console, so it will run only on the unix machine
Basing on those info, can you give me an idea about how could I try to do it?
(On my Windows machine client, I did it using PCFILES which ran on my PC, but of course it is no longer an option since PCFILES can't run on unix)
I hope I made myself clear.
01-31-2014 04:43 PM
Rather than updating an existing Excel template, it might be easier to just use tagsets.excelxp to create the file you want. It writes an xml file that Excel is happy to open. And you can do a LOT of formatting of cells (colors, cell types, etc etc).
I don't know if it's possible to do what you want in your environment (without access to PCFILES). We have a 64 bit unix machine. But it can access a windows server to use the PCFILES engine. look forward to hearing from others.
01-31-2014 06:05 PM
If the BI Platform is involved and the SAS Add-in for Microsoft Office, you cannot have a stored process use TAGSETS.EXCELXP, except through the Information Delivery Portal. The BI Platform and stored processes complicate the answer. I still think that working with Tech Support is the best bet.