Hi,
It looks like PROC IMPORT's option MIXED = YES doesn't work with Excel 2007. It works perfectly with Excel 2003.
Is there any work around?
I don't want to assign format in data step for 100 variables.
Define "doesn't work". Do you get error messages? Unexpected result?
If you show the complete syntax and post an example of the data it doesn't work for you may get a more helpful response, otherwise you'll be getting guesses.
It works fine if I remove MIXED = YES option. But then SAS doesn't read the correct values.
According to the documentation DBMS=XLSX only accepts getnames, range and sheets. Have you tried either DBMS=Excel or DBMS=Excelcs?
What version of SAS are you using?
MIXED= only works with DBMS=EXCEL setting from the documentation in SAS 9.3.
You might try adjusting the TypeGuessRow registry key setting for the version of Office you have (online SAS help has key depending on versions installed). The default setting by OFFICE, not SAS, is apparently 8 rows to guess whether a column is text or numeric on export.
As further clarification MIXED = works with DBMS = XLS and XLSX in SAS 9.3 regardless of what the documentation says. In SAS 9.4 only MIXED = with DBMS = EXCEL now works, you will get an error if you use the other types. So only 9.4 is consistent with the documention.
We struck this problem while testing our SAS 9.3 to 9.4 migration.
DBMS = EXCEL should work for most Excel versions. I've tested it successfully with 2010.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.