BookmarkSubscribeRSS Feed
Tonio
Calcite | Level 5

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

3 REPLIES 3
ballardw
Super User

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.

Tonio
Calcite | Level 5

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.

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4589 views
  • 1 like
  • 2 in conversation