topic Re: How to read the Excel with over 256 columns by SAS in SAS Programming

How to read the Excel with over 256 columns by SAS

zheng2017 — Wed, 18 Jan 2017 09:18:23 GMT

Hi,

Recently , I got confused by importing the Excel dataset with hundreds of variables by using SAS.

When I tried to do it , SAS imported the Excel dataset with only 256 variables and droped many variables without error messages printed in the log.

Is there anyone who knows the methods of importing Excel data with over 300 variables?

I'm looking forward to your answers.

Thanks.

Re: How to read the Excel with over 256 columns by SAS

RW9 — Wed, 18 Jan 2017 09:25:59 GMT

You can use the search functionality on here to see all the other posts in the same topic as you are looking, here for example;

https://communities.sas.com/t5/SAS-Procedures/Limit-To-Columns-Imported-from-Excel-2010/td-p/109430

What version of SAS are you using, what OS are you using, what code are you using? Can you not get a CSV file and do a proper import of data?

Also, to note, a dataset with more than about 50 columns becomes programatically very unweildy to use, consider data re-modelling to get it into a more usable format.

Re: How to read the Excel with over 256 columns by SAS

zheng2017 — Wed, 18 Jan 2017 09:28:49 GMT

OS: Windows
SAS Version:9.3

For somes reasons,I can't convert Excel into CSV format.

Re: How to read the Excel with over 256 columns by SAS

RW9 — Wed, 18 Jan 2017 09:44:52 GMT

Open Excel, SaveAs, then in the Filetype box under filename, select CSV and Save.

Re: How to read the Excel with over 256 columns by SAS

ballardw — Wed, 18 Jan 2017 14:52:55 GMT

Along with @RW9 excellet suggestion after saving the file to CSV open it in a text editor such as Wordpad or Notepad. You will want to look for things where the first column doesn't look correct. If people have entered text, or exported text, containing linefeeds or carriage returns then you may have lines split in the CSV file. Solutions for this may depend on how many cases appear.

Also you may find rows at the end consisting of nothing but:

,,,,,,,,,,,,,,,,,,,,,,,,

from rows deleted in Excel. You should delete those before importing into SAS.

Re: How to read the Excel with over 256 columns by SAS

art297 — Wed, 18 Jan 2017 18:17:43 GMT

You haven't posted the code you've tried but, as mentioned in the post that @RW9 referenced, try DBMS=XLSX

Art, CEO, AnalystFinder.com

Re: How to read the Excel with over 256 columns by SAS

zheng2017 — Thu, 19 Jan 2017 08:58:23 GMT

The following code is the way I import the excel into SAS:
libname EXL excel "D:\tt\dt.xlsx"
ver=2010 SCAN_TEXTSIZE=NO MSENGINE= ACE ;

Re: How to read the Excel with over 256 columns by SAS

art297 — Thu, 19 Jan 2017 15:22:17 GMT

Use XLSX engine in the libname statement. See: http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Art, CEO, AnalystFinder.com