How to Import Excel 2010 worksheet (.xlsx) that has more than 255 columns into SAS 9.4

[hungling108]

1 PROC IMPORT OUT= WORK.b1
2 DATAFILE= "D:\Pap10501_CGMA\test\dRI1.xlsx"
3 DBMS=EXCEL REPLACE;
4 RANGE="Sheet1$";
5 GETNAMES=NO;
6 MIXED=NO;
7 SCANTEXT=YES;
8 USEDATE=YES;
9 SCANTIME=YES;
10 RUN;

NOTE: create WORK.B1 dataset。
NOTE: dataset WORK.B1 has 3693 observations and 255 variables.

 Actually, excel file has 2000 columns.

[RW9]

Hi,

Sorry what does this mean:

4 RANGE="工作表1$";

A range options specifies which cell range to use for import, its likely you just nee to update that.  However that being said, you would probably be better off saving the XLSX as CSV, and then writing a datastep import, as you then have much more control over it.  I.e.

data imported_data;
  infile "<your_csv>.csv";
  informat col1 ...;
  format col1 ...;    
  input  col1 $
           col2 
           col3 $
...;
run;

Howevet that being said, why do you have a spreadsheet with 2000 columns?  Nobody is ever going to review that amount of information, assess where the data has come from, and wether you can get the data in a better format than that, i.e. if it came from a database, export from the database a normalised data structure directly to CSV.

[hungling108]

The excel file is downloaded from datastream database.

I have to deal with those files into one sas file.

The line 4 RANGE="工作表1$";  ==> RANGE="Sheet1$";

[RW9]

Try adding dbms=excel to your code.

https://communities.sas.com/t5/SAS-Procedures/Importing-a-xlsx-file-into-SAS/td-p/113434

[hungling108]

I do it in line 3.

[Kurt_Bremser]

Then take Excel out of it altogether. Unload from the database into a sensible, character-based format, and import that into SAS.

Excel is a format for spreadsheets, not for database data.

[Tom · Accepted solution]

Try using DBMS=XLSX instead of DBMS=EXCEL. That will tell SAS to use a non-microsoft method to read the XLSX file.

[hungling108]

Thanka for everyone reply. 

The following code is successful for importing Excel (.xlsx) into SAS 9.4.

DBMS=XLSX;

==> The import data set has 70167 observations and 2001 variables.