Export multiple large SAS datasets into excel pivot table

Reply
Contributor
Posts: 23

Export multiple large SAS datasets into excel pivot table

Hello all,

I have a few SAS datasets created in SAS EG that I want to feed into excel pivot table. Each SAS dataset contains about 2.8 MM rows (the model being built requires high granularity and hence the large number of rows). I started with the following code:

proc export

  data=work.sas_dataset

  dbms=xlsx

  outfile='/users/excel_output.xlsx'

  replace;

run;

Challenge with this code is that it copies data into a spreadsheet and Excel 2010 limits the number of rows to 1.05 MM therefore data exported is not complete.

Another option is to use export function in EG to save data as mdb file that can then be imported into Excel pivot without having to show the raw data in Excel. This method works fine but it is manual and I am hoping to eliminate manual intervention as much as possible.

3rd option I thought about is to modify the data source identifier (dbms =) in the above code so that it outputs mdb file instead of xlsx. However the "access" option for mdb file is only available under 32 bit operation system and I am on Win 7 64 bit.

Based on the circumstances, what is my best option?

Thanks,

Tao

Super User
Posts: 11,134

Re: Export multiple large SAS datasets into excel pivot table

You're actually Pivoting that many records in Excel?

I would think another option would be to create any summary tables in SAS and export those.

Contributor
Posts: 23

Re: Export multiple large SAS datasets into excel pivot table

The tool being built has many dimensions and multiple key performance metrics to report on. The large number of records is primarily a result of the permutation/combination of segment variable that is desired from user standpoint.

The suggested method makes sense but I need to anticipate the views preferred by user and it will have to change from time to time whenever there is new view requested and not included.

I am sure there are other ways to slim down the data set as well which I will put more effort into. Right now, I am testing the "balance" between granularity and maintenance effort, and seeking alternative so that the end solution can be robust.

Super User
Posts: 19,167

Re: Export multiple large SAS datasets into excel pivot table

You should look into the SAS Microsoft Add-In.

Are you using PowerPivot? You might be able to connect directly to a DB via powerpivot that will update automatically as well.

Ask a Question
Discussion stats
  • 3 replies
  • 502 views
  • 0 likes
  • 3 in conversation