topic Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables in SAS Programming

Exporting from SAS to Excel Worksheet without overwriting formatting and tables

elwayfan446 — Thu, 08 Oct 2020 14:59:01 GMT

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!

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

art297 — Thu, 08 Oct 2020 15:14:07 GMT

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

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

elwayfan446 — Thu, 08 Oct 2020 15:29:13 GMT

Without reading through this in depth, is this a completely hands off approach? Everything is automated in my process right now. The scheduler runs a process flow in Enterprise Guide (which kicks off a program with the proc export above). Then the worksheet is downloaded to the network.

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? 🙂

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

Kurt_Bremser — Thu, 08 Oct 2020 15:35:40 GMT

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.

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

elwayfan446 — Thu, 08 Oct 2020 15:39:13 GMT

Yes. There is much more formatting flexibility within excel than the options in Enterprise Guide that I’ve found. If there is something better in EG I’m missing then that would we great.

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

art297 — Thu, 08 Oct 2020 15:49:51 GMT

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.