Is it possible (and how) to automatically save and close Excel after opening a file (like in this example: https://communities.sas.com/t5/General-SAS-Programming/opening-an-excel-file-with-SAS/m-p/260934#M36...) using an x command?
Why? SAS generates output, what you do with that output afterwards is not a SAS issue. I mean you can pass out system commands (well if you have the correct permissions) and open Excel, but then you need to send the commands through to Excel to do something. The question would be why you would want to do this, I assume its something to do with updating Excel specific things like pivots or something? If so then have the Excel file have some On Open VBA code which loads your data into it and then refreshes the workbook. This is far simpler than any other method. Another way is to have a VB batch file which send the appropriate commands out, there are hundreds of examples out there:
This could be called from SAS.
But again, it all depends on use case scenario and best fit.
Why? SAS generates output, what you do with that output afterwards is not a SAS issue. I mean you can pass out system commands (well if you have the correct permissions) and open Excel, but then you need to send the commands through to Excel to do something. The question would be why you would want to do this, I assume its something to do with updating Excel specific things like pivots or something? If so then have the Excel file have some On Open VBA code which loads your data into it and then refreshes the workbook. This is far simpler than any other method. Another way is to have a VB batch file which send the appropriate commands out, there are hundreds of examples out there:
This could be called from SAS.
But again, it all depends on use case scenario and best fit.
It is actually intended as a (makeshift auto-)update and I was hoping to avoid DDE etc.
Yes, then build your auto update process using Excel and VBA. With that you can create an OnOpen function (note this does not have to be part of the actual SAS file, the VAB can be in a separate file) which opens the CSV file you exported from SAS, reads that in and processes it into your Excel file, then refresh all the objects within the Excel file and save it. Its pretty simple, and you can kick it off from SAS just by asking the OS to open the working file - which would kick off its OneOpen function, load CSV, update other Excel file, refresh then save and shut down.
I would highly advise to avoid DDE.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.