Hello,
I currently export from SAS to excel with a proc export procedure.
PROC EXPORT DATA=msrflow.msrflow_batch_totals
outfile= &exportoutfiletot
dbms=xlsx replace;
sheet="DLY_BATCH_TOTALS";
run;
Right now it works as intended. The udpated data replaces the sheet that is in the current workbook. In the actual excel file I am trying to create dynamic pivot tables on a separate sheet that will auto update when the data in the sheet is updated from SAS. I have not found a great way to do this dynamically. I was trying to format the data that is updated as a table in excel so the pivot tables would dynamically update, however, each time I export fresh data from SAS, the formatted table of the data is overwritten with just the raw data.
My ultimate question here is how can I export the data so all of my pivot tables will work correctly with updated data from the sheet I am exporting to?
Thanks!
If you're on Windows then the following macro might be what you need:
https://github.com/FriedEgg/Papers/tree/master/Excelling_to_Another_Level_with_SAS/doc
HTH,
Art
If the purpose of all this is to create a pdf document from SAS data, you can do that in SAS and avoid the Excel detour.
I don't think the macro will work with Enterprise Guide. If you have access to a copy of SAS, on either a Windows-based machine or Windows-based server, the macro would do what you want. It's intended to submit code, like your proc export code, but that uses vb script to (1) copy and paste your data to a range that you specify (i.e., not a predefined range); (2) not alter existing formats; and/or (3) use an existing workbook or template as a template to paste data into a range.
Yes, it can be run as a totally hands-off approach.
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!
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.