- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have a power shell script that opens the workbook and prints the first sheet as a pdf and saves it to another location. The problem is that once the power shell opens the workbook and it’s refreshed, none of the pivot tables on that first sheet are updated because the new data in the sheets it is coming from has been over written with the new data from the export.
Clear as mud? 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.