How to read the Excel with over 256 columns by SAS

[zheng2017]

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.

[RW9]

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.

[zheng2017]

OS: Windows
SAS Version:9.3

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

[RW9]

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

[ballardw]

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.

[art297]

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

[zheng2017]

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 ;

[art297]

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