topic Re: PROC DBLOAD in Microsoft Integration with SAS

PROC DBLOAD

deleted_user — Wed, 14 Nov 2007 15:00:29 GMT

Using SAS 8.2--> SAS/EIS specifically on a Unix environment, where the users submit their query with a remote submit, and where the results are placed on a local path on there pc.

When they use the Export to Excel function, the user will receive a windows screen with an error. When I look at the SAS-logs it seems that, with the export function via PROC DBLOAD, reaches a maximum limit within EXCEL of 16.383 rows. This is documented by Microsoft, but than it's said that this limit is reached with the columns. So i'm starting to get confused.

This is the SAS-log:
ERROR: The maximum number of rows has been exceeded, no more observations will be loaded.
ERROR: Insert failed for obs number 16384.

Then again, I'm trying to export the same data as a SAS Data Set. No problem so far. The next step is to use the Export function that's available in the toolbar. I export the SAS dataset as a .xls file--> no problem.
I'm openingen the newly created .xls form in Excel 2000 and all data is shown. --> finally no problem. But still, via the 'normal'users way of working, it fails with the column maximum of 16.383, while the used variables are rows....

proc dbload data=WORK._EXPORT_
dbms=excel ;
path = "M:\DOCUME~1\xxxxxxx\LOCALS~1\Temp\3\_SAS0002.XLS";
version=/*5*/7;
limit=0;
putnames=yes;
label;
WARNING: Label set. Use reset command to use SAS labels.
reset ALL;
load;
run;

Who has an idea of what isn't working proparly? Thanks in advance!

Daniël@LogicaCMG

Re: PROC DBLOAD

deleted_user — Thu, 15 Nov 2007 01:54:14 GMT

16384 is 16k, and is the maximum number of rows that a given release of Excel could handle in its spreadsheet. I think it was Excel 97. This was fixed in Excel 2000, I think it was doubled. It has also been changed again in subsequent releases.

The problem has been that M$oft changed the triplets to Excel with Excel 95 and SAS has had difficulty keeping up with Excel. I suspect that you are facing an issue arising from these limitations, and the versions of the procedures available for each spreadsheet.

I think a call to Tech Support is needed to clarify this.

Kind regards

David

Re: PROC DBLOAD

deleted_user — Mon, 19 Nov 2007 08:57:36 GMT

Thnx for your input. I do understand the gap between this specific Microsoft Excel and SAS software. So a call will be in it's place for further info.

Kind Regards,
Daniel

Re: PROC DBLOAD

rob_dunn — Wed, 23 Sep 2009 04:46:30 GMT

I just got the same problem SAS 9.1 Excel 2002

ERROR: The maximum number of rows has been exceeded, no more observations will be loaded.
ERROR: Insert failed for obs number 16384.

My boss informed me that the limit is 60,000. Do you know if this is correct?

Re: PROC DBLOAD

LinusH — Wed, 23 Sep 2009 09:02:04 GMT

According SAS on-line doc, for DBLOAD, only Excel versions up to Excel 95 i supported, which probably mean that SAS can't create spreadsheets larger than 16k rows. So you are might better off by trying out a different way to export your data.

/Linus

Re: PROC DBLOAD

Bill — Thu, 24 Sep 2009 13:07:21 GMT

I routinely "export" more than 16k rows using one of these two methods (SAS9.1)

PROC EXPORT DATA= WORK.CLM
OUTFILE= "\\technology\qualshare\sresults\Qsrptsys\Data\clm.xls"
DBMS=EXCEL2000 REPLACE;
RUN;

or

ods html file="\\technology\qualshare\group\reports\Rejects.xls";
proc print/tabulate etc;
run;
ods html close;