05-09-2014 08:53 AM
First off, I'm using SAS v6.100 for 1-2 weeks and have only brief understanding of SAS structure and approach to data mining, analysis etc. Also, I hope my questions are not very often seen (at least I've found none similar) and I'm not creating another discussion with the same question.
Is it possible to connect an Excel PivotTable directly to data sets (not OLAP cubes!), which are created as a result of any kind of Projects?
I suggest the answer is yes. However, I lack of knowledge in different kind of drivers, which should be used in Excel to connect to those SAS Data sets (for example, I have a result saved as "cds.sas7bdat" and in Excel I have these:
SAS IOM Data Provider 9.4
SAS Local Data Provider 9.4
SAS OLAP Data provider 9.4
SAS SHARE Data Provider 9.4).
I've found a lot of different codes, which create Excels and Pivots from SAS data. But it would be nicer to have Template-PivotTables, which are refreshed manually after a Project completed in SAS - this way I would save a lot of time spent for formatting Excel-file.
Please, give me some links with more information about creating connections between Excel and SAS.
Thank You in advance!
05-12-2014 03:40 AM
To start SAS V6 is an old version as of 1990's. You are using SAS Eguide V6.1 with a SAS 9.4 Server. That is one difficult thing with SAS all those versions of the components.
MDDB (Multi Dimensional Data Bases), Olap - Cubes and Pivot tables (Microsoft) are all the same concepts. Microsoft did the MDX query language as an extension on top of SQL.
When you have Eguide 6.1 you could also be licensed to AMO (Addin Microsoft Office) that tool is integrating SAS and MS-office including Excel. Accessing SAS-datasets is a standard feature.
With Excel/MS-office you can connect to databases and more. You are needing to understand those connections.
SAS IOM Data Provider == A connection to SAS service on a Server. The workspace server is a sample (SASapp in Eguide). You can program to this interface.
SAS Local Data Provider 9.4 == A connection to SAS service on your local machine. The behavior is similar as a workspace server. You can program to this interface.
SAS OLAP Data provider 9.4 == An interface to cubes defined at a SAS server. Having an Etl process in place with cubes you can access that.
SAS SHARE Data Provider 9 == An SQL interface to access SAS data. The requirement is that the SAS-datasets are defined by a sas-platfomr-admin in a SAS-Share server
If your only requirement is exporting a SAS dataset to Excel why do you not use export / import?
05-12-2014 04:32 AM
Well, from my side if I wanted a spreadsheet, which updates itself based on some data then think about it from the other way round. Create your spreadsheet as you would like to see it, formatting, layouts, graphs etc.. Setup a VBA macro which runs on startup and loads an Excel file located in a specific place. That other Excel file will contain the data used to create pivots/graphs etc. Then all you would need to do is run the export from SAS as and when you like, overwriting the data Excel grid, and each time you open the main Excel file it would open the data as it is and update.
05-12-2014 03:37 AM
Have you tried loading the SAS Add-In for Microsoft Office? That would allow you to "see" the data in a SAS table as columns in an Excel spreadsheet, and you could define your pivot table over these columns.