How to update a dashboard in excel with SAS data.

[renjithr]

Hi,

I am looking for options to update an excel dashboard( contins comparion of regions for each month, graphs etc) with SAS data. I heard people saying this can be done using  VBA but I do not have any VBA experience. Is  there any good resources that I can utilize? 

Please advise.

[Reeza]

It depends on your dashboard structure and your data. 

If your data is well structured, ie the number of rows and columns are fixed then you can do something like the following. 

1. Set up a template in Excel - graphs linked to data source in separate Excel sheet

2. In template, set up named ranges in Excel

3. Use libname method to export data to named ranges in Excel

If you don't have a well structured data sets you will have to use VBA. 

I presented a paper on this, but I'm not sure it stands alone very well. It does have sample code however.

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhu...

[renjithr]

Thank you Reeza. I plan to use the technique you mentioned in your paper for a dashbord where the columns and rows are static.

Any reccomendatins ( links, books ) for creating dynamic dashboards using VBA?

[Reeza]

No...I'd ask that question in an Excel forum to get a response. 

I do use it in other reports, but its incredibly custom so it depends. Excel also supports slicers and the Power Pivot tool, which allow for dynamic reporting so that VBA may not be required at all.

Again it depends on what you're trying to do 🙂

[renjithr]

Thank you!