BookmarkSubscribeRSS Feed
AleksejsS
Calcite | Level 5

Hello everyone!

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!

Cheers,

Aleksejs

4 REPLIES 4
AleksejsS
Calcite | Level 5

Have I asked in wrong section of the forum? Or is the question unclear/ out of Your competencies?

jakarman
Barite | Level 11

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?  

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RichardinOz
Quartz | Level 8

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.

Richard

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 10316 views
  • 2 likes
  • 4 in conversation