topic Re: Export sas table to excel and create a pivot code in SAS Enterprise Guide

Export sas table to excel and create a pivot code

subrat1 — Wed, 20 Jul 2016 04:37:10 GMT

I have sas table called: EXPOSURE, I want to write a program in sas to export it to excel hidden sheet called 'premium' and want to create a pivot as shown below.

It should also show the list of value in filter drop down.

MAPPED_COVRG	(All)
RISK_STATE_CD	(All)
YQTR	Sum of EARNED_EXPOSURE	Sum of WRITTEN_EXPOSURE	Sum of WRITTEN_PREMIUM	Sum of EARNED_PREMIUM
2006Q1	72,538	81,953	19,952,490	17,344,365
2006Q2	80,291	105,219	25,223,946	19,261,388
2006Q3	89,390	107,191	25,492,690	21,529,784
2006Q4	97,905	112,642	27,227,132	23,607,847
2007Q1	105,448	123,325	29,741,038	25,450,573

Re: Export sas table to excel and create a pivot code

Reeza — Wed, 20 Jul 2016 05:48:23 GMT

This isn't a trivial task.

If you search on here you'll find some solutions for this situation, but the main issues are

1) export to specific location

2) create/update pivot table

A common work around is to create a template that is updated, source data replaced and there's an embedded Excel macro that refreshes the pivot table. Given that your using EG, you can't use DDE. Can you use an Excel macro? How are you envisioning this process working on your side?

Re: Export sas table to excel and create a pivot code

LinusH — Wed, 20 Jul 2016 21:28:17 GMT

Sounds like a job for the SAS Addin for MS Office.
With that you could either let your pivot access SAS data directly, or have a Stored Process update the sheet data.