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

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

hungling108 — Fri, 19 Feb 2016 13:52:38 GMT

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.

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

RW9 — Fri, 19 Feb 2016 13:42:50 GMT

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.

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

hungling108 — Fri, 19 Feb 2016 13:55:27 GMT

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

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

RW9 — Fri, 19 Feb 2016 13:58:19 GMT

Try adding dbms=excel to your code.

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

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

Kurt_Bremser — Fri, 19 Feb 2016 13:58:25 GMT

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.

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

hungling108 — Fri, 19 Feb 2016 14:00:59 GMT

I do it in line 3.

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

Tom — Fri, 19 Feb 2016 14:23:47 GMT

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

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

hungling108 — Sat, 20 Feb 2016 05:23:33 GMT

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.