BookmarkSubscribeRSS Feed
CHalla1
Calcite | Level 5

I have an excel spreadsheet template that has to be filled in every week with SAS data. Way back when, I used DDE to do this and place data in pre-defined cells in the excel template. Since DDE is gone to pasture, I'd like to be able to do this with ODS excel if that is possible. Anyone have an idea of how I would start. 

 

I use ODS Excel all the time to create fresh excel tables but never for a pre-defined excel spreadsheet template.

 

Help.

8 REPLIES 8
SASKiwi
PROC Star

Unfortunately that is not possible. It is one of the restrictions of ODS EXCEL that it has to build workbooks from scratch. When you think about it, ODS EXCEL is just generator of XML that the Excel application renders as a workbook, so it would be way too unreliable and tricky to try and decipher existing XML to figure out what has to be amended or added.

 

An alternative approach could be to use PROC EXPORT to add or replace an existing data sheet (just simple rows and columns of data), then have formulas and / or macros in the workbook to copy the data over to the custom template.

Patrick
Opal | Level 21

I've seen that done the way @SASKiwi explains. 

1. create a copy of your template

2. Use Proc Export and add data sheets

 

I consider it a sound design to keep report layout (structure) and data in separate sheets.

 

If you want to directly modify cell values in a existing sheet as you could using dde then look into Python package openpyxl that provides such functionality.

I've used this package in the past for use cases where ods excel didn't provide what I needed (i.e. creating Excels with lookup validations). SAS and Python work nicely together and even though I'm on a Python junior level but I was so far capable to "Google" all I needed to get to a solution.

KevStopsk
Calcite | Level 5

One trick I've found helpful is to first make sure your Excel template is set up exactly how you want your final output to look. This includes all your formatting, headers, and even formulas if needed. Once that's set, you can use ODS Excel to populate your data into the right cells.

 

I also found that checking out excel online tutorials can give you some great insights and tips on optimizing your template. You may want to experiment with PROC REPORT to control exactly where each piece of data lands in your template. Sometimes small adjustments, like ensuring that column widths in SAS match those in Excel, can make a big difference.

Ksharp
Super User

You could try UPDATE statement of PROC SQL.

 

libname x excel 'c:\temp\temp.xlsx' scan_text=no getnames=no;

proc sql;
update  x.'Sheet1$'n
 set F1='02' 
  where F3='2';
quit;

libname x clear;

Ksharp_0-1704174904981.png

 

CHalla1
Calcite | Level 5

Interesting. What if I wanted to write data at D3?

Ksharp
Super User

Sorry . You can't , due to there is no such variable in dataset after importing SAS from Excel.

 

Ksharp_0-1704244909973.png

 

Tom
Super User Tom
Super User

This can only work if you can run EXCEL.  

 

What you showed will work when SAS is running on a Windows machine that also has a compatible version of EXCEL that it can execute.

 

You might also be able to use EXCELCS libref engine if you can get SAS PC FILES SERVER working on a machine that has EXCEL and also has access to the XLSX file you want to update.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2593 views
  • 5 likes
  • 7 in conversation