SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

[ Edited ]

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.

 


Accepted Solutions
Solution
‎02-20-2016 12:23 AM
Super User
Super User
Posts: 6,495

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

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

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.

New Contributor
Posts: 4

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

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$";

Super User
Super User
Posts: 7,392

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

New Contributor
Posts: 4

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

I do it in line 3.
Super User
Posts: 6,927

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎02-20-2016 12:23 AM
Super User
Super User
Posts: 6,495

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

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

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

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 582 views
  • 3 likes
  • 4 in conversation