04-25-2013 04:45 AM
I have a dataset with around 20,00000 observations.
Now I want to create a pivot table in excel with these observations but I cant export it in excel since excel have only 10,00000 rows
Is there a way to create a external connection or something and just create pivots in excle by referencing it to sas dataset
Any help will be appriciated...Thanks in advance :-0
04-25-2013 04:52 AM
For that, you need SAS Add-on for MS Office, and at least SAS BI Server where the data resides.
I'n not sure how large you table is, 20....? It's preferable to either define your table in an Information Map, or store the data in a OLAP Cube.
04-25-2013 07:47 AM
Probably, then you need SAS/ACCESS to PC File Formats.
Another option (haven't done it since long ago), is to use the SAS ODBC/OLEDB driver to access the data. Again, you need some specific SAS server license to utilize that.
04-25-2013 07:49 AM
Correction, a work around (ok if this is one time job) is to export SAS data to a csv file or whatever you can import into MS Access.
04-25-2013 08:20 AM
I have done the updating of data in an Access DB from sas (using SAS/Access) and had Excel just use that DB as an external data source for the pivot. I think I may have had to write some VBA to have excel refresh the pivot table automatically when the file was opened. I believe I had 3 or 4 pivot tables running off different tables in the DB because of differences of the data being displayed.
The only thing that comes to mind to watch out for is not to change the table name or variable names once the pivot is set up or you will have to redefine those for each change.