SAS 9.4: PROC IMPORT for .xlsx (what happened to F1, F2, F3, ...?)

Mean Joe

TS Contributor
#1
I am not liking SAS 9.4, wish I could stay with 9.2.

Anyone have a workaround for these problems?

Background on my .xlsx: column headings start with numbers, eg 9220184.
That is a SNP number, so I plan to write code to append "rs" to the beginning of the name. But I cannot import the whole file.

1) The import wizard is not liking .xlsx format. Help (http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/)?
CONNECTION FAILED: See Log for details.
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.


I try some other things...

2) Usually I use PROC IMPORT with DBMS=EXCELCS (one of those darn SAS 9.4 changes). I've also tried DBMS=XLSX and DBMS=XLS.
But one of the problems I'm having is that SAS won't read the variable names as you know, 9220184 is an invalid SAS name so SAS changes it to _220184.

3) So I try GETNAMES=NO option, but not available with certain DBMS= options. And I try OPTIONS validvarname=any statement. In the rare times that these statements are accepted by the different SAS DBMS= options, I get the other problem: SAS only importing 255 columns. And this problem, I give up on trying to solve. Any workarounds to suggest? Specifically looking for SAS code; don't bother suggesting any file manipulations.
 

Mean Joe

TS Contributor
#2
I forgot to add: When I use the GETNAMES=NO option here, the variables get named "A","B",..."Z","AA",...
Which is really a pain; I prefer them to be named F1, F2, F3, ... like in older SAS.
 

noetsi

Fortran must die
#3
Probably not a work around you will want to use, but I don't think enterprise guid has this issue. I have SAS 9.4 and EG 6.1 which I believe uses 9.4 (it uses whatever version you have and I have 9.4). I regularly import XLSX files into EG with no problems. Any code you do in 9.4 you can do in EG simply by creating a code window. Then code whatever you were going to do.

I knew programers who programed in EG, while never using the GUI, simply because they preferred the EG editor. Essentially a code window is just an editor - which does not use the GUI at all as far as I know.
 

hlsmith

Not a robit
#4
My license comes with EG, but it always goes so slow. Sorry, no help, I have had a copy of 9.4 unistalled next to my desk for a year. Dang admin rights and time to install!!! Yeah, go ol F1,..,Fi :)
 

noetsi

Fortran must die
#5
With some very rare exceptions my EG runs fast (I don't use 9.4 outside EG enough to know how they compare, but they could hardly be faster). However, I rarely run the more complex forms of stats, mainly I use EFA or some form of regression, rarely ARIMA or ESM.

I found a link that says this for 9.4. I doubt it matters, but figure I would show it.

By specifying DBMS=EXCEL, the IMPORT procedure can read any version of these files that are saved in Microsoft Excel workbooks. If you specify DBMS=XLSX, the IMPORT procedure can read only EXCEL2007 or later versions of the files that are saved in Microsoft Excel workbooks.
 
#8
I had the same exact problem when I was trying to run (in 9.4) the code I had written for 9.2.

I saw that others suggested SAS Enterprise Guide (EG) as a solution, but I haven't had luck with that. Also, I have also tried what 'noetsi' suggested, "By specifying DBMS=EXCEL, the IMPORT procedure can read any version of these files that are saved in Microsoft Excel workbooks. If you specify DBMS=XLSX, the IMPORT procedure can read only EXCEL2007 or later versions of the files that are saved in Microsoft Excel workbooks." That didn't work for me either.

Have you found a solution to the problem? If so, can you post it?

Many thanks!


I am not liking SAS 9.4, wish I could stay with 9.2.

Anyone have a workaround for these problems?

Background on my .xlsx: column headings start with numbers, eg 9220184.
That is a SNP number, so I plan to write code to append "rs" to the beginning of the name. But I cannot import the whole file.

1) The import wizard is not liking .xlsx format. Help (http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/)?
CONNECTION FAILED: See Log for details.
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.


I try some other things...

2) Usually I use PROC IMPORT with DBMS=EXCELCS (one of those darn SAS 9.4 changes). I've also tried DBMS=XLSX and DBMS=XLS.
But one of the problems I'm having is that SAS won't read the variable names as you know, 9220184 is an invalid SAS name so SAS changes it to _220184.

3) So I try GETNAMES=NO option, but not available with certain DBMS= options. And I try OPTIONS validvarname=any statement. In the rare times that these statements are accepted by the different SAS DBMS= options, I get the other problem: SAS only importing 255 columns. And this problem, I give up on trying to solve. Any workarounds to suggest? Specifically looking for SAS code; don't bother suggesting any file manipulations.
 

noetsi

Fortran must die
#9
I am really amazed you could not import it into EG. I have never encountered this issue and I import xlsx. files all the time. What happens when you try?