SAS data to excel

sharedobe — Tue, 16 May 2017 23:14:36 GMT

Dear SAS Community,

I have been encountering a problem in exporting an excel dataset from SAS.

I have a dataset that is needed to be exported monthly. As time goes by, the number of rows in that dataset grows.

The size of the dataset is usually less than 4,000 KB with around 30,000 rows.

Sometimes, the dataset can be exported fine in a xlsb format with all the rows that I want. But some other months (maybe by the end of the year when the number of rows grows), it is exported only a few rows of the complete dataset. However, it doesn't give me any error message. My current solution to this is open the SAS dataset in SAS and export it as a xlsb format manually.

Any thoughts about fixing this problem or what causes this problem?

I am using the following code to export the dataset

PROC EXPORT DATA= myDataset
OUTFILE="&mypath._&Report_Year.&Report_Month..xlsb"
DBMS=EXCEL2007 REPLACE;
SHEET="DATA";
RUN;

data _null_; x = sleep(30); run; /*I was hoping putting a Pause may help to resolve the issue */

Thank you for your time and your help!

Re: SAS data to excel

Reeza — Tue, 16 May 2017 23:50:42 GMT

Pause should have no effect on a PROC EXPORT. Try using DBMS=XLSX instead.

Excel2007 is old and I believe had the 65k limit. I think 2010 64 bit is a million rows. 30k shouldn't cause issues though.

topic SAS data to excel in SAS Enterprise Guide

SAS data to excel

Re: SAS data to excel