topic Re: Proc Import Excel - Reading Formula Problem in SAS Data Management

Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 11:19:59 GMT

Hello,

We are using PROC IMPORT for Excel and reading excel formulas as cell value. As far as we have a numeric return value as a result of this formula then getting right results in our data set. But the problem is when we have text value as a result of this formula then not recieving the result text. It seams Proc Import for excel formula function only if the return value is numeric.

How to solve this problem for getting the text values as a result of excel formula? or it is not possible?

For example:

A1 has formula =F2

A2 has formula =F3

Cell Values

F2 has value 1234

F3 has value ABC

When we run proc import then getting the following results:

Correct: A1 = 1234

Wrong: A2 = here come nothing or some strange text???

Any help would be greatly appreciated.

Dani

Re: Proc Import Excel - Reading Formula Problem

Ksharp — Tue, 10 Jun 2014 12:17:06 GMT

add mixed=yes option in proc import .

Xia Keshan

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 12:32:21 GMT

I just tried it with mixed=yes option in proc import but unfortunately its not helping.

Re: Proc Import Excel - Reading Formula Problem

LinusH — Tue, 10 Jun 2014 12:40:23 GMT

It works for me.

Please attach the log, with your code.

proc import dbms=excel file='C:\temp\test formler.xlsx' out=test;

mixed=yes;

run;

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 13:23:08 GMT

Thanks for quick responses.

Please apply it with different excel sheets. It means if you execute same proc import again for another excel sheet with different values then you will not get the new values for new sheet. It returns only the value which read first time.

Re: Proc Import Excel - Reading Formula Problem

LinusH — Tue, 10 Jun 2014 13:28:32 GMT

Not quite following you.

Can you describe step by step how to reproduce your problem?

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 13:44:30 GMT

Excel file name is myfile.xlsx and sheet name is firstsheet.

This runs fine and return the correct values from sheet=firstsheet.

proc import

datafile="myfile.xlsx"

out=work.myreport

dbms=XLSX replace;

sheet=firstsheet;

getnames=no;

mixed=yes;

run;

Excel file name is same myfile.xlsx and sheet name is secondsheet.

Now if you create another sheet=secondsheet; change the excel entries in this sheet and run the following for secondsheet. you will not get the result of secondsheet. It will still show you the result of firstsheet.

proc import

datafile="myfile.xlsx"

out=work.myreport

dbms=XLSX replace;

sheet=secondsheet;

getnames=no;

mixed=yes;

run;

I hope you will understand the problem now.

Re: Proc Import Excel - Reading Formula Problem

Reeza — Tue, 10 Jun 2014 14:39:05 GMT

Your DBMS is set to XLSX rather than Excel. Try switching it.

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 14:42:38 GMT

As I mentioned I am reading XLSX file (myfile.xlsx). DBMS = XLSX is right.

Re: Proc Import Excel - Reading Formula Problem

Reeza — Tue, 10 Jun 2014 14:50:18 GMT

So you tried it and it didn't work?

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 14:54:07 GMT

Yes.

DBMS = EXCEL don't work for XLSX files.

Re: Proc Import Excel - Reading Formula Problem

Reeza — Tue, 10 Jun 2014 14:58:13 GMT

What version of Excel, SAS are you on? And what bit(s)? 32 or 64 bit for SAS and/or Excel?

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 15:06:55 GMT

Excel 2010

SAS Enterprise Guide, Version 5.1 Hot fix 16 (32-Bit)

Re: Proc Import Excel - Reading Formula Problem

Reeza — Tue, 10 Jun 2014 15:09:57 GMT

Use the Wizard then. It allows you to specify the type of variable in the wizard and make sure to select character.

Re: Proc Import Excel - Reading Formula Problem

danimian — Tue, 10 Jun 2014 15:15:28 GMT

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?

Re: Proc Import Excel - Reading Formula Problem

stat_sas — Tue, 10 Jun 2014 15:49:44 GMT

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;

Re: Proc Import Excel - Reading Formula Problem

ballardw — Tue, 10 Jun 2014 20:23:06 GMT

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.

Re: Proc Import Excel - Reading Formula Problem

Reeza — Tue, 10 Jun 2014 20:29:28 GMT

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.

Re: Proc Import Excel - Reading Formula Problem

LinusH — Wed, 11 Jun 2014 08:38:14 GMT

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.

Re: Proc Import Excel - Reading Formula Problem

Ksharp — Thu, 12 Jun 2014 13:26:29 GMT

Did you try Libname statement ?

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