turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- Proc Import Excel - Reading Formula Problem

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 07:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 08:17 AM

add mixed=yes option in proc import .

Xia Keshan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 08:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 08:40 AM

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;

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 09:23 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 09:28 AM

Not quite following you.

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

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 09:44 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-13-2014 06:42 PM

You might want to report that to SAS Support. Technical Support Form

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 10:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 10:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 10:50 AM

So you tried it and it didn't work?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 10:54 AM

Yes.

DBMS = EXCEL don't work for XLSX files.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 10:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 11:06 AM

Excel 2010

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-10-2014 11:09 AM

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