As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Handling too-big Excel exports

From sasCommunity
Jump to: navigation, search

A common task requirement is to deliver a body of data in an Excel file. That's pretty straightforward, unless the number of observations exceeds Excel's row capacity.

When there are too many rows, a common response is to just break the data into chunks, storing each chunk in a separate worksheet (tab). That's easy to do, but the result may not be very useful. Excel users may end up doing a lot of repetitious manual processing and consolidation.

Often, a better solution is to export the data from SAS to Microsoft Access, so that the Excel end user can use Excel's capacity to query external data sources.

Example:

libname ax_s access 'c:\temp\msq\ax_s.mdb';
 
proc datasets library=ax_s;
delete class;
run;
quit;
 
data ax_s.class;
recordnumber + 1;
set sashelp.class;
run;
 
libname ax_s clear;

Including serial numbers (RECORDNUMBER) makes it possible to extract arbitrary chunks if that ultimately proves necessary.

Here are the steps to take on the Excel side:

  • Open Excel
  • In the Data menu, choose Get External Data, then New Database Query
  • In the Choose Data Source box, select the Databases tab
  • Select MS Access Database, then click OK
  • In the Select Database dialog, Navigate to the .MDB file (in this example, c:\temp\msq\ax_s.mdb)
  • Click OK to start the wizard
  • Follow the wizard's prompts to filter the data and create a subset which Excel can accommodate
  • In the Finish dialog, pick Return Data to Microsoft Excel, then Finish
  • Tell Excel where to place data

Subsequently, to change the filter, select any cell(s) in the range holding the data and from the Data menu pick Get External Data then Edit Query

Notes

  • These instructions are for Excel 2000. Details may differ in other versions.
  • The Microsoft Query application (part of Microsoft Office) must be installed.

The wizard is somewhat limited. In particular, it does not assist the user in aggregating the data. If one escapes the confines of the wizard, far more versatility becomes available. There is a query builder resembling the one in Microsoft Access. Or, one can use SQL. However, these tools may impose a pretty steep learning curve for a lot of Excel users. If you can anticipate the summarizations which will be needed, it may be better to implement them as queries in the MS Access file and let the end users refer to them by name in the Excel wizard.