05-20-2014 10:11 AM
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?_
05-20-2014 10:26 AM
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.
05-20-2014 10:59 AM
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.
05-20-2014 11:11 AM
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;
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).
05-20-2014 11:13 AM
05-22-2014 04:00 PM
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.
05-23-2014 03:50 AM
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.
05-23-2014 05:03 AM
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.
05-23-2014 07:05 AM
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.
05-27-2014 02:06 PM
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.