Corrupted excel file when using proc export, but sas shows no errors...

Reply
New Contributor
Posts: 2

Corrupted excel file when using proc export, but sas shows no errors...

I am using SAS 9.3. I often have to export data to an excel file. To do this I have been using proc export with the dbms = xlsx. I initially found that unless I added sheet = 'sheet name' the file would not open and say it was corrupted and needed to be repaired. I've since made sure to add this sheet name everytime I export. Generally, proc export works fine, but every now and then I run into the same issue where the excel file says it is corrupted. I don't get any errors in sas when I do this, in fact, it always tells me the file was created successfully. The last time this happened, I did notice that the data file was fairly large (it really varies from project to project), probably around 500,000 line items. I don't know if having large data could be an issue. Any ideas on how to fix this? Here is an example of the code I use:

proc export data = population

                          outfile = "Path name here\The file name.xlsx"

                          dbms = xlsx replace;

sheet='Population';

run;

Also, I was wondering if there is a way to export multiple sheets into the same file. When I was using 9.2, I could just use dbms = excel, and it would add a new tab each time I ran the code (keeping the file name and path the same and changing the sheet name and data to put into it. The xlsx option for the dbms however doesn't seem to work the same way. Any suggestions?

Thanks,

Tony

Grand Advisor
Posts: 10,211

Re: Corrupted excel file when using proc export, but sas shows no errors...

In Excel 2007 and 2010, the maximum worksheet size is 1048576 rows by 16384 columns. So if some of your projects are exceeding either of those limits, though row is by far the most common limit hit, that could be the problem.

Row limits have always been a problem with spreadsheets.

New Contributor
Posts: 2

Re: Corrupted excel file when using proc export, but sas shows no errors...

Right, I wasn't exceding those limits, would be nice if the fix were that easy, but unfortunately I fear it is a bit more complicated. Though, I really don't know why it won't occasionally work.

Grand Advisor
Posts: 10,211

Re: Corrupted excel file when using proc export, but sas shows no errors...

If you aren't exceeding those limits it may be the content of one or more records. If I wanted see if that were the case I would be tempted to export portions of the data set, using FirstObs and OBS dataset options and see if the problem is restricted to specific records.

for instance

proc export data = population (obs=1) /* to limit to the first record*/

                          outfile = "Path name here\The file name .xlsx"

                          dbms = xlsx replace;

sheet='Population';

run;

proc export data = population (obs=240000) /* to limit to the first 240000 records*/

                          outfile = "Path name here\The file name a.xlsx"

                          dbms = xlsx replace;

sheet='Population';

run;

proc export data = population (firstobs=240001) /* to make a separate file of those after 240000*/

                          outfile = "Path name here\The file name b.xlsx"

                          dbms = xlsx replace;

sheet='Population';

run;

And to find if the problem exists within a specific range

proc export data = population (firatobs=100000 obs=240000) /* first record is 100000 and export additional  140000 records*/

                          outfile = "Path name here\The file name a.xlsx"

                          dbms = xlsx replace;

sheet='Population';

run;

Ask a Question
Discussion stats
  • 3 replies
  • 1560 views
  • 1 like
  • 2 in conversation