BookmarkSubscribeRSS Feed
Neo
Calcite | Level 5 Neo
Calcite | Level 5

Hi all,

 

I have an excel(2007) workbook with 6 sheets. All contain data. Now i want to replace that data in all the tabs with the data that i have in 6 datasets in SAS.

 

Kindly help me with this.

 

Best Regards,

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why do you want to replace it?  If you have the data in SAS, then just export your data to Excel using proc export, ods tagsets.excelxp, libname excel etc.  If you need to keep it as Excel 2007 (and why?) then use proc export.  If it is a template Excel file that you need to update then that is very different.  Personally I would suggest exporting the data from SAS to CSV, then in your Excel file, write some VBA code to open those CSV files and populate the Excel file as needed.  The reason being is that Excel is unstructured/controlled, the data could be anywhere, in any format and if your forced to use that application, then use the functionality within that app to do the task, rather than try to mash it through another app.

Neo
Calcite | Level 5 Neo
Calcite | Level 5
It is a standard template actually. And I send that as a report every month
with updated data. First, I export the data from SAS EG and then copy paste
the data in each sheet in the template manually, so I just wanted to
automate it.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, common thing this.  Look at what you do each time, is it exactly the same cells?  Excel is so loose that you might have to alter it each time anyway.  Now DDE does still work, for how long?  Its very old, and doesn't work on some setups.  Again, I would still go with my original suggestion.  Write some VBA, doesn't even have to be in that Excel file, a separate one which loads that file up, then loads the CSV data, then runs through some VBA code to put the data from the CSV where it should go, then save the file.  Its pretty simple.

Reeza
Super User

I'm using DDE on Office 2013 with SAS 9.4 so it's still working 🙂

Reeza
Super User

Here's two references that may be helpful for you:

 

 

This is a macro based on VBScript that allows export to specific cells and/or an Excel Template

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Here's an Excel DDE sample code:

http://www.ats.ucla.edu/stat/sas/faq/sas2excel_dde.htm

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!

Discussion stats
  • 5 replies
  • 2023 views
  • 0 likes
  • 3 in conversation