DATA Step, Macro, Functions and more

How to read the Excel with over 256 columns by SAS

Reply
New Contributor
Posts: 3

How to read the Excel with over 256 columns by SAS

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.

Super User
Super User
Posts: 7,942

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

Posted in reply to zheng2017

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.

New Contributor
Posts: 3

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

OS: Windows
SAS Version:9.3

For somes reasons,I can't convert Excel into CSV format.
Super User
Super User
Posts: 7,942

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

Posted in reply to zheng2017

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

Super User
Posts: 11,343

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

Posted in reply to zheng2017

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.

PROC Star
Posts: 7,468

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

Posted in reply to zheng2017

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

 

New Contributor
Posts: 3

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

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 ;
PROC Star
Posts: 7,468

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

Posted in reply to zheng2017

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

 

Ask a Question
Discussion stats
  • 7 replies
  • 304 views
  • 0 likes
  • 4 in conversation