What is the most efficient way to export a SAS Dataset of 40 million records?

[Benbo123321]

SAS newbie here.  I'm trying to export a SAS dataset of approximately 40 million observations and 110 variables  so that it can be read by someone with Excel or Access.

I really want to avoid making 21 excel workbooks with 1,048,575 records and 1 workbook of 154,150 records.

Also, how do I retain the header row for each subset? 

Thanks in advance.

Ben

[RW9]

Why on earth would you want to put 40mil rows out to Excel??  I doubt even the most ethusiatic person is going to want to see that kind of data.

You *could* do it:

ods tagsets.excelxp file="...xls";

ods tagsets.excelxp (sheet="Sheet1" auto_Filter=yes);

proc report... first observations....;

ods tagset.excelxp (sheet="Sheet2"...

However you are pretty much going to break it.  40 million records is going to be a huge gigabyte spanning monstrosity which Excel probably wont even be able to open.

[art297]

I think the answer will depend upon how the new workbook(s)/worksheet(s) will be used.  Since Excel can handle 1,048,576 rows with 16,384 columns each, you could put 148 observations on each row, and thus end up only using up 270,271 rows.

[Benbo123321]

Would Access be better? how would I go about doing that?

Ben

[art297]

I think that Access would be limited to 2GB and no fields could be greater than 255 characters. If you exceed those limitations, than no.

If you are within those limits, and license SAS/Access for PC File Formats, you might be able to get away with something as simple as proc export.

[RW9]

It really depends on what you intend doing with the data.  As I always say Excel is not designed for <insert your task here> in any way shape or form.  Then fact that it can be shoehorned in just shows how bad and dangerous it is.

If you are looking to store that data, then use database or data warehouse, there are free ones out there and they should easily deal with that amount of data.  For special needs then choose a proprietary one and get a license.  Trust me DB's are designed for storing data, use a tool for its purpose.

If you are analysing the data then keep it within SAS.  Produce summarised reports from it to send out.

For any other needs see what you can do to minimise the output.  For instance in my industry we capture subject data.  It wouldn't be beneficial to just dump all the data out to Excel, so Profiles are generated.  A nice structured layout per subject so reviewers can actually see the data easily.  If someone sent me a spreadsheet with 40 mil rows, or 16k columns I would start sharpening the trusty pitchfork :smileydevil:

Also, final note.  If you export the data via tagset to Excel, it wont really matter so much what Excel's limitations are, XML is the most verbose transfer format around so your file size will be vast.

[ballardw]

I'm not sure on the current limits of a single MS Access table but the process would be:

Export to CSV

open Access

tell it to import the CSV.

Or if you have the correct PC/Files modules you can use Proc Export with an appropriate DBMS, OUTFILE and OUTTABELE options.