DATA Step, Macro, Functions and more

max number of variables sas can import from excel

Reply
Super Contributor
Posts: 413

max number of variables sas can import from excel

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

Regular Contributor
Posts: 215

Re: max number of variables sas can import from excel

Super User
Posts: 17,737

Re: max number of variables sas can import from excel

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

Super Contributor
Posts: 413

Re: max number of variables sas can import from excel

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.

PROC Star
Posts: 7,356

Re: max number of variables sas can import from excel

@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

 

Super Contributor
Posts: 413

Re: max number of variables sas can import from excel

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

Super User
Super User
Posts: 7,392

Re: max number of variables sas can import from excel

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.

Super User
Posts: 17,737

Re: max number of variables sas can import from excel


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. 

Super User
Super User
Posts: 6,495

Re: max number of variables sas can import from excel

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. 

Super User
Super User
Posts: 7,392

Re: max number of variables sas can import from excel

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?

PROC Star
Posts: 7,356

Re: max number of variables sas can import from excel

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

Ask a Question
Discussion stats
  • 10 replies
  • 256 views
  • 0 likes
  • 6 in conversation