BookmarkSubscribeRSS Feed
ilikesas
Barite | Level 11

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

15 REPLIES 15
Reeza
Super User

How are you importing your Excel file? Use DBMS=XLSX. 

ilikesas
Barite | Level 11

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.

art297
Opal | Level 21

@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

 

ilikesas
Barite | Level 11

@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!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

@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. 

Tom
Super User Tom
Super User

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

art297
Opal | Level 21

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

MPZ_VET
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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?

MPZ_VET
Calcite | Level 5

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.

SAS.jpg

 

Tom
Super User Tom
Super User
Try being specific that you want it to use the XLSX engine instead of asking Microsoft Jet engine to translate the file.
proc import datafile='filename.xlsx' dbms=xlsx ....

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 6698 views
  • 0 likes
  • 8 in conversation