BookmarkSubscribeRSS Feed
elwayfan446
Barite | Level 11

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!

5 REPLIES 5
elwayfan446
Barite | Level 11
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? 🙂
elwayfan446
Barite | Level 11
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.
art297
Opal | Level 21

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.

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1663 views
  • 0 likes
  • 3 in conversation