topic Re: Limit To Columns Imported from Excel 2010? in SAS Procedures

Limit To Columns Imported from Excel 2010?

shellp55 — Sun, 28 Oct 2012 23:30:46 GMT

Hello

I am importing a table from Excel 2010 using SAS 9.2. This has never been a problem before but now SAS isn't importing all columns. The table is from A1 to OQ366 and SAS only imports to IU366. I even forced the range by using the worksheet and name in the import syntax i.e.

"ImportFile$A1:OQ366". For another table that is A1 to NB366 it only imports to MH366.

Is there a specified limit to the number of columns imported? Is there any way I can force the import? Thanks.

Re: Limit To Columns Imported from Excel 2010?

art297 — Sun, 28 Oct 2012 23:46:37 GMT

Post the code that you used. Column IU, which I think is column 255, makes me think that you are attempting to import using one of the older engines. Are you running base SAS or are you using EG? Do you license SAS/Access for pcfile formats?

Re: Limit To Columns Imported from Excel 2010?

PGStats — Mon, 29 Oct 2012 01:18:22 GMT

I believe the ACE library (a Microsoft product) which is the interface used by SAS to access Excel and Access files is still limited to 255 column on import and export.

Re: Limit To Columns Imported from Excel 2010?

art297 — Mon, 29 Oct 2012 04:11:06 GMT

I think the libname engine still has those limits but, with the latest version of 9.2 (M 3 I think), proc import and proc export can deal with the actual new excel capabilities.

Re: Limit To Columns Imported from Excel 2010?

shellp55 — Mon, 29 Oct 2012 13:10:14 GMT

The code I used to import is:

Proc IMPORT OUT= WORK.MASTER_NEW_B1

DATAFILE= &import_path

DBMS=EXCELCS REPLACE;

Range = "B1$";

Scantext = Yes;

UseDate = Yes;

run;

Note that B1 is the name of the worksheet. So is there any way to get around the import column limit?

Thanks.

Shelley

Re: Limit To Columns Imported from Excel 2010?

art297 — Mon, 29 Oct 2012 14:01:35 GMT

I can only test it on 9.3. The following worked for me on a 64-bit Windows 2008 server:

%let import_path=d:\art\widetest.xlsx;

Proc IMPORT OUT= WORK.MASTER_NEW_B1

DATAFILE= "&import_path"

DBMS=XLSX REPLACE;

getnames=no;

run;

Re: Limit To Columns Imported from Excel 2010?

shellp55 — Mon, 29 Oct 2012 16:33:21 GMT

Hi Arthur

Thanks so much....I don't know why I said I had 9.2 because I do have 9.3!! I'm assuming the "getnames" is for the column head labels?

Re: Limit To Columns Imported from Excel 2010?

art297 — Mon, 29 Oct 2012 18:16:59 GMT

Yes. If you have column head labels then use getnames=yes;