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,
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.
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.
I'm using DDE on Office 2013 with SAS 9.4 so it's still working 🙂
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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!