BookmarkSubscribeRSS Feed
chimanbj
Obsidian | Level 7
I have a very specific Excel template that I need to populate, but I am running on a Unix system, so I won't be able to use DDE. Can I use ODS to put my report in specific cells?
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
Hi:
ODS methods of creating files for Excel do not populate existing Excel files or templates. ODS creates HTML or XML or CSV files that Excel knows how to open and render. Each time that ODS creates a file -- if the file name does not exist in the specified location, then ODS writes the file; if the file name DOES exist in the specified location, then ODS overwrites the existing file with the newer results.

It may be possible for you to create a CSV (comma separated value) file with ODS and then link that CSV file to your Excel template. Or, if your template has named ranges, you may be able to use the SAS Excel LIBNAME engine or PROC EXPORT to export data to named ranges.

Depending on how complex your Excel template is, you may find that you can recreate it entirely using ODS TAGSETS.EXCELXP or ODS TAGSETS.MSOFFICE2K_X, which create Office 2003 XML and Office HTML files respectively. Without any idea of what the Excel template is or how it looks or what function/purpose it serves, it is hard to make more concrete suggestions.

cynthia
SASJedi
SAS Super FREQ

If the ODS solution is not feasible, check to see if you have licensed SAS/Access to PC File Formats for your Unix box, and if so, verify that the the SAS administrator has installed and configured the PC File server that comes with the SAS/Access product on a Windows box on your network.  With this infrastructure in place, you can read and write to Excel spreadsheets from your Unix SAS session using a LIBNAME statement.  The LIBNAME statement would look something like this:

libname xlr PCFILES path="D:\folder\MyFile.xls" server=<server name> port=<port#>;

You would replace <serve name> with the name of your server (somethin like pcff.mydomain.com) and <port#> with the port number used to access the PC File server (the default value is 8621). 

If you want to write data to specific cell ranges in the Excel spreadsheet, it is best to set up NAMED RANGES in the spreadsheet first. When you assign a libname to an Excel workbook that contains named ranges, each named range appears as a separate data set in the SAS library.  Due to limitations of the MS Jet Engine, which SAS Access uses to read and write to Excel, you can't overwrite data in the existing cells - you first have to empty the range then write in the new data.

For example, say the workbook MyFile.xls contains a named range called "PutDataHere" and you want to fill it with the data from SASHELP.CLASS.  The following code should do the trick:

libname xlr PCFILES path="D:\folder\MyFile.xls" server=pcff.mydomain.com port=8621;
proc sql;
   drop table xls.PutDataHere;
quit;
data xls.PutDataHere;
   set sashelp.class;
run;
Check out my Jedi SAS Tricks for SAS Users

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
  • 2 replies
  • 2267 views
  • 0 likes
  • 3 in conversation