SAS EG import of spreadsheet with Excel IFERROR() function does not work properly

[cau83]

A colleague of mine was using EG and importing a spreadsheet (with the SAS EG Import task) that contained data in hardcoded numbers and binary 1/0 columns that were produced by nested IFERROR(IF()) functions. The cells with IFERROR were (correctly) detected as having numeric data, but the data set created had 5 for every value in those columns. He found a workaround by removing IFERROR and only using IF(), but that could result in errors in the workbook.

Other possibly important information:

SAS EG 4.3, SAS 9.2, Excel file was stored on Microsoft Sharepoint.

Any ideas on this? Can SAS not handle IFERROR or is there something else? Anyone ever had a formula column get pulled in as all 5's or any number?_

[RW9]

Personally I would drop the Excel calculation columns completely and focusing on the data itself.  I.e. once it is read in do some error checking in your code.  Alternatively save the Excel file as CSV and then read that in so you have control over it.  Would be worth seeing if the ISERROR function resolves to 5 in a csv, or if that is something on the SAS read mode.

[Reeza]

Can you post an example of the full formula used in Excel, or a sample of the workbook?

[cau83]

IFERROR(IF(A2>0,1,0),0)

where A2 could have an error because it was a lookup to somewhere else on another sheet. Let's assume IFERROR is necessary in the spreadsheet.

[RW9]

Just tried it in base SAS - created an XLSX with three rows of numerb data and had this formula on four rows, i.e. the final row is an error.  Then ran:

proc import datafile="s:\temp\rob\tmp.xlsx" out=tmp replace;

  getnames=no;

run;

And it imported successfully, with the right value.  Are you using XLSX, if so all I can impute is its something to do with EG (which I don't use).

[Reeza]

No issues when I do it with my sample workbook.

How are you importing?

Using SAS 9.3 and Excel 2010.

Generally I agree with though, read in your raw data and do the processing in SAS.

[cau83]

In this case, it's not an option to "do the processing" in SAS. this is a shared workbook (hence why it was on sharepoint), used in part by non-SAS users.

We are using Excel 2007, SAS 9.2.

It is an .xlsx file. We are importing using an EG import task (proc import wouldn't be an option because the file is not stored on the SAS server).

I delayed in responding while we did some more testing here. 3 users all have the problem as described above. I, on the other hand, import it as missing data-- the import task reads that column as a Character variable with a length of 1, and all the values are missing (and if i change it to numeric in the import it's also still missing). So now I'm wondering why it works differently for me and why it reads in as missing.

[RW9]

Probably you have an option setup locally to your machine which may affect it.  I am afraid I can't really help further in that respect.  However I can suggest that if your really have to use Excel, then you could pop an On Close VBA macro into the spreadsheet which automatically saves a .CSV as well as the file being saved.  That way you would only need to look at the CSV and avoid the horrors of Excel.

[Kurt_Bremser]

I have found through dire experience that the only half-way reliable process for importing/exporting Excel data is through csv. Everything else is dependent on Windows version, Office version, EG version, local setup of the PC, and time of day/season of year/gold price.

If you are a SAS administrator/developer, don't get into the business of fixing Ballmer&Co's stupidities.

[jakarman]

let us see: XLSX file that is a zip-file containing a structure with XML-files.

MS  (ex Ballmer&CO) is letting you having read that with their ACE driver. The old SAS interface with SAS/bases is using that one (2003 Excel compatible).

The excel-file is coming from SharePoint. I do not know the ACE driver is used with that. I think it is needed to do a download.  

Having your xlsx file uploaded to the server your file can be read at the serverside even with UNIX. SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

The recalculation of cells is an Excel function possible solved with ACE, but should that when reading the xml in that zip file direct?
Must SAS solve the spread issues.. that makes no sense. By the way Excel is no database.

[cau83]

We (my company) does not have SAS/ACCESS(R) 9.3 Interface to PC Files. (Just this morning I was having a discussion with a colleague about how they can't do something because we do not have it, so hopefully we'll add it).

It appears that the general consensus is that Microsoft is likely the culprit (either directly or indirectly) and that we should avoid using it when working with SAS. I don't disagree with the latter sentiment. Thanks.