topic Re: MIXED = YES doesn't work with Excel 2007 in SAS Procedures

MIXED = YES doesn't work with Excel 2007

Dreamer — Fri, 24 Oct 2014 15:31:03 GMT

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.

Re: MIXED = YES doesn't work with Excel 2007

ballardw — Fri, 24 Oct 2014 17:08:56 GMT

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.

Re: MIXED = YES doesn't work with Excel 2007

Dreamer — Tue, 28 Oct 2014 18:41:44 GMT

It gives error!!

Code:

PROC IMPORT OUT=Work.Temp

DATAFILE="<<Workbook Location>>"

DBMS=XLSX REPLACE;

MIXED=YES;

SHEET=SHEET1;

RUN;

Log:

26	MIXED=YES;
	_____
	180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: MIXED = YES doesn't work with Excel 2007

Dreamer — Tue, 28 Oct 2014 18:42:51 GMT

It works fine if I remove MIXED = YES option. But then SAS doesn't read the correct values.

Re: MIXED = YES doesn't work with Excel 2007

art297 — Tue, 28 Oct 2014 18:54:35 GMT

According to the documentation DBMS=XLSX only accepts getnames, range and sheets. Have you tried either DBMS=Excel or DBMS=Excelcs?

Re: MIXED = YES doesn't work with Excel 2007

Dreamer — Tue, 28 Oct 2014 18:59:14 GMT

Hi ,

DBMS=EXCEL option is for Excel 2003 and it works fine if I save my workbooks in that format.

But my excels are in 2007 format.

I have already tried DBMS=Excelcs with no luck!!

Re: MIXED = YES doesn't work with Excel 2007

art297 — Tue, 28 Oct 2014 19:01:57 GMT

What version of SAS are you using?

Re: MIXED = YES doesn't work with Excel 2007

ballardw — Tue, 28 Oct 2014 19:41:03 GMT

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.

Re: MIXED = YES doesn't work with Excel 2007

SASKiwi — Tue, 28 Oct 2014 20:18:18 GMT

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.