Creating Pivot through SAS dataset

Reply
New Contributor
Posts: 3

Creating Pivot through SAS dataset

Hi Team,

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

DInesh

Super User
Posts: 5,260

Re: Creating Pivot through SAS dataset

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.

Data never sleeps
New Contributor
Posts: 3

Re: Creating Pivot through SAS dataset

Hi LinusH,

Thanks for replying.

Can I export the data in Access .mbd format and then create a pivot through that

Please suggest

Thanks

Dinesh

Super User
Posts: 5,260

Re: Creating Pivot through SAS dataset

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.

Data never sleeps
Super User
Posts: 5,260

Re: Creating Pivot through SAS dataset

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.

Data never sleeps
Super Contributor
Posts: 333

Re: Creating Pivot through SAS dataset

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.

EJ

New Contributor
Posts: 3

Re: Creating Pivot through SAS dataset

Thanks ejack01,

Yes I have used the same method and refreshed the tables using vba. I was haveing around 50 tables in my excel


Ask a Question
Discussion stats
  • 6 replies
  • 1719 views
  • 3 likes
  • 3 in conversation