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.
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.
Open Excel, SaveAs, then in the Filetype box under filename, select CSV and Save.
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.
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.