Hi,
I am inporting an Excel data set having not many observations (88), but many variables (453). I import it with the import wizard, but then see that only 255 variables were imported. Is this the limit of variables that can be imported from Excel, becasue if I am not mistaken SAS can handle more than 255 variables?
Thank you
Hi, perhaps this post will resolve your problem?
https://communities.sas.com/t5/SAS-Procedures/proc-import-won-t-read-all-columns/td-p/15895
How are you importing your Excel file? Use DBMS=XLSX.
Hi Reeza,
I am importing the file by clicking File --> import data ...
I didn't import the file by coding, but I will try it.
@ilikesas: I like to keep up with current versions of SAS that people are using, thus would appreciate it if you would post a snap shot of what the screen looks like after you click 'IMPORT'
Appreciated,
Art, CEO, AnalystFinder.com
@art297 : I tried to take a snapshot of my screen but for some reason didn't get anything...
But there might be no need for a screenshot; after I finished the import procedure SAS tells me that the data set was successfully created and that it contains x observations and 255 variables.
real time 10.25 seconds
cpu time 1.92 seconds
On the other hand when I imported the data using Proc Import I was able to get all 453 variables! SAS first says that the data set has x observations and 453 variables, and on the next line it says that the data set was successfully created.
real time 0.96 seconds
cpu time 0.7 seconds
I am using SAS 9.3
Hope this was helpful, please let me know if there is any other information that I might be able to give you!
453 variables, that sounds like a really good setup and one not prone to errors and difficult coding.
If it was me I send the file back and ask them to specify a useable datafile.
@RW9 wrote:
453 variables, that sounds like a really good setup and one not prone to errors and difficult coding.
If it was me I send the file back and ask them to specify a useable datafile.
Does that actually work for you? In most places, you'd get in trouble for that and usually you have no choice. I know what I'd say if one of my analysts refused to work with an Excel file. I don't see any risk in asking but sadly Excel is the dominant analysis tool in business and they have bigger worries.
It depends on the type of project. If this is one-off request then it is probably faster to find a way to use the existing file. But if they are trying to setup a process that will be used many times then it is worth taking the time to set up data collection in a way that can work well for everyone that uses the data.
Yep, I am in Pharma, and it is very important to get data in following specific industry standards, following repeatble validatable methodology. To this end we draw up import agreements which specify file formats, structure, codelists and such like. Anyone sending me Excel files would cause me to throw toys out of the cradle, at minimum doubling any effort needed to work with these things. I understand it is different in finance where mostly everything is done on Excel.
What bigger worry is there than the data you base your company on being wrong? Maybe that pivot table is more important?
I agree with @Reeza. Use the XLSX engine. I presume it can handle the same limitations as an Excel 2013-2016 workbook, namely:
1,048,576 rows by 16,384 columns (see: https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269... )
Art, CEO, AnalystFinder.com
I have had the same problem, and it was solved by using PROC IMPORT, and the file as csv. It won't work when importing the .xlsx version, will keep importing only 255 variables, then save the file as .csv and imported again and all variables (383) get imported correctly.
Perhaps you can provide the details of how you tried to import the original spreadsheet that resulted in only some of the columns being included. If you are using a point and click interface explain what you clicked on and what options you selected. You can post screen shot using the Insert Photo icon in the forum editor.
Include details or what version of SAS you are running. Something like 9.4M6 for example. What user interface are you using to connect to SAS? Are you using SAS Display Manager? SAS Enterprise Guide? SAS/Studio? If one of the last two what version of those tools are you using? What operating system is your user interface running on? What operating system is your SAS process running on?
I first tried using the import wizard: File\Import Data\standard data source\"browse file". and only the first 255/382 variables imported.
then I've tried PROC IMPORT datafile="C:\Users\.....\file.xlsx" and the same outcome, 255/382.
Finally I save the .xlsx filer as .csv, run the PROC IMPORT datafile="C:\Users\.....\file.csv" and all the variables were imported as suposed.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: