Help using Base SAS procedures

Limit To Columns Imported from Excel 2010?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 152
Accepted Solution

Limit To Columns Imported from Excel 2010?

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$A1Smiley SurprisedQ366". 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.


Accepted Solutions
Solution
‎10-29-2012 10:01 AM
PROC Star
Posts: 7,364

Re: Limit To Columns Imported from Excel 2010?

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;

View solution in original post


All Replies
PROC Star
Posts: 7,364

Re: Limit To Columns Imported from Excel 2010?

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?

Respected Advisor
Posts: 4,655

Re: Limit To Columns Imported from Excel 2010?

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.

PG

PG
PROC Star
Posts: 7,364

Re: Limit To Columns Imported from Excel 2010?

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.

Regular Contributor
Posts: 152

Re: Limit To Columns Imported from Excel 2010?

Hi

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

Solution
‎10-29-2012 10:01 AM
PROC Star
Posts: 7,364

Re: Limit To Columns Imported from Excel 2010?

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;

Regular Contributor
Posts: 152

Re: Limit To Columns Imported from Excel 2010?

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?

PROC Star
Posts: 7,364

Re: Limit To Columns Imported from Excel 2010?

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 4283 views
  • 3 likes
  • 3 in conversation