Proc Import Excel - Reading Formula Problem

[danimian]

Using the wizard is not a solution for us. I am using the code in SAS DI Studio 4.6 in a User Written Transformation.

Is it Bug which has to be fix or there is any other solution?

Did you try the scenaio of proc import which i provided in early emails and which result you are getting?

[Reeza]

You have two issues.

XLSX does not support SHEET= as far as I know, it seems to support only single sheet workbooks, which is also why I've been suggesting to change your DBMS.

Excel is supposed to work for XLSX files according to the documentation as well.

The second issue is if one field in one sheet has all numbers, SAS will import that as numbers.

If the second sheet then has a field with mixed characters it will then read that as characters and the formats will not match.

I don't think importing excel sheets is something I would ever do in DI studio...doesn't seem like a good sustainable long term solution, more of a hack.

[LinusH]

Totally agree with Reeza. Excel file are not reliable as a source. Who is delivering this file? Who is responsible for updating it? How often do you need to import it?

Preferable to find some other kind of solution.

If you still need to use Excel, using the libname excel engine is more meta data driven than using User Written code.

[Ksharp]

Did you try Libname statement ?

libname x xls 'c:\x.xls' mixed=yes ;

[danimian]

I tried all options but it seems like a sas problem/limitation.

Just solved the problem with temporary solution (removing the formula and reading all the values as text without formula). Working well but not a fine solution.

Thanks a lot to all for your response and quick support.

[MarkNicholas]

Bringing this up again.  May I know if any new option/way is available to solve this issue from proc import code. with a mix of formula and number.

[LinusH]

You give us too little feed-back when you are just saying "I tried all options".

Perhaps following s advice is your best option.

[Tom]

If you have a column with some cells with numbers and some cells with text then SAS makes the column character.

If you have a column with all cells formulas and all of the results are text then SAS makes the column character.

If you have a column with all cells formulas and all of the results are numbers then SAS makes the column numeric.

The problem is that when you have a column where every cell is a formula and some of the formula generate numbers then SAS makes the column numeric.  You should report this to tech support.

A simple solution is to add a dummy row (without formulas) of values that allow SAS to set the column type.

[RW9]

A simple solution would be to stop using some half-assed, unstructured software for data.  Save the file to CSV so that the formulas are resolved, then write a datastep import  which reads the data as per the agreement you have with the vendor.  If there are invalid data in there, i.e. they do not conform to the structure document, then send the file back and ask source vendor to supply a corrected file, or update the transfer specifications. 

The problem is no transfer specifications/agreement and an unacceptable transfer format, much like every question raised on Excel use

[stat_sas]

Try this, may be helpful in solving the problem.

proc import
datafile=datafile="myfile.xlsx"
out=work.myreport
dbms=EXCELCS replace;
RANGE="firstsheet$";
SCANTEXT=YES;
run;

proc import
datafile=datafile="myfile.xlsx"
out=work.myreport
dbms=EXCELCS replace;
RANGE="secondsheet$";
SCANTEXT=YES;
run;

[ballardw]

An underlying issue with understanding the differences in Excel and SAS:

Columns in SAS are a single type numeric or character, Excel can change cell by cell. In your starting example the first is numeric and SAS probably expects the remainder of Column A to be numeric. Since there is no way to treat "ABC" as a number it will be missing.

Mixed will read the column allowing both data types BUT the result is likely to be character variable.