Desktop productivity for business analysts and programmers

Export sas table to excel and create a pivot code

Reply
Contributor
Posts: 32

Export sas table to excel and create a pivot code

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_EXPOSURESum 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

 

Attachment
Grand Advisor
Posts: 17,294

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

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?

Esteemed Advisor
Posts: 5,194

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

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.
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 209 views
  • 0 likes
  • 3 in conversation