BookmarkSubscribeRSS Feed
Edoedoedo
Pyrite | Level 9

Hi everybody,

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

Regards

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

Hi:

  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.
 

cynthia

Edoedoedo
Pyrite | Level 9

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.

Thanks again

Regards

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Cynthia_sas
SAS Super FREQ

Hi:

  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.

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1141 views
  • 0 likes
  • 3 in conversation