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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- import excel data with formulas

- 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

04-28-2014 07:55 AM

Hi,

I want to import data from excel that is formulated into SAS.

for example:

A B

1 8 =A1+1

2 9 =A2+1

3 4 =A3+1

4 5 =A4+1

Now I want to copy the whole thing into SAS but for some reason column B shows grand total, am using the proc import method to import the whole excel file.

Any help will be appreciated.

Many thanks

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

Posted in reply to Sophie_12

04-28-2014 08:33 AM

H,

Yes that is default behavior as far as I am aware. The formulas are resolved by Excel on save and that is an Excel feature. Why would you need Excel specific formula to be imported into your SAS program? The only alternative I can think of off the top of my head is to set all your formulas to text in Excel, then import that, however the question remains, why?

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

Posted in reply to Sophie_12

04-28-2014 08:39 AM

Direct import only transfers values, not formulas.

If you want to get the formulas, you could decompress the .xlsx and then work through the .xml files in the tree.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

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

Posted in reply to Sophie_12

04-28-2014 09:40 AM

Sophie

Databases like SAS do not use cell references like spreadsheets. Each column is treated as a variable, and the formulas work on variables rather than individual cells. Thus the SAS formula equivalent to your example is

B = A + 1 ;

(the same for every row) and it would be part of SAS datastep following your import from Excel.

If you were to import the formulas as text, as suggested by @RW9 , all you would have is a column containing the text of the Excel formulas. These values would have no computational significance in SAS and cannot readily be used to calculate resulting values from them.

Now I am supposing that your real world example contains much more complex formulas than the simple example you have shown, and you may want to replicate such a formula in SAS. If that is the case you may, with perhaps some difficulty, be able to convert an Excel formula into an equivalent SAS formula. If you care to post an example closer to your real world formula that you want to "import" and we may be able to suggest the equivalent SAS code. Any Excel functions in your formula will need to be replaced with SAS equivalents or code to provide the same outturn.

Richard

Message was edited by: Richard Carson

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

Posted in reply to RichardinOz

04-28-2014 09:11 PM

I dont want the formula but the values that is calculated by the formula in excel.

For eg I have something like this:

A B C

1 1/2/13 yes =IF(AND(A1 <> ".",B1 <>""),"YES","NO")

2 2/3/12 No =IF(AND(A2 <> ".",B2 <>""),"YES","NO")

3 4/5/14 Yes =IF(AND(A3 <> ".",B 3<>""),"YES","NO")

So I want to import the data with the values calculated by the formula in C which is just yes,no and yes... and I have to use the excel file in XLSX format with multiple sheets

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

Posted in reply to Sophie_12

04-28-2014 09:26 PM

That is the default SAS behaviour as specified.

If it's not working, then perhaps you should elaborate on your problem.

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

Posted in reply to Reeza

04-29-2014 04:01 AM

Concur with Reeza, I have put that data into and XLSX file and imported with:

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

sheet="Sheet1";

getnames=no;

run;

And I get a file with three columns, the last populated by 'YES' data. This is expected operation. In your original mail it seems to indicate you didn't want the result but the formula?