topic import excel data with formulas in SAS Programming

import excel data with formulas

Sophie_12 — Mon, 28 Apr 2014 11:55:26 GMT

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

Re: import excel data with formulas

RW9 — Mon, 28 Apr 2014 12:33:21 GMT

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?

Re: import excel data with formulas

Kurt_Bremser — Mon, 28 Apr 2014 12:39:02 GMT

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.

Re: import excel data with formulas

RichardinOz — Mon, 28 Apr 2014 13:40:37 GMT

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

Re: import excel data with formulas

Sophie_12 — Tue, 29 Apr 2014 01:11:23 GMT

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

Re: import excel data with formulas

Reeza — Tue, 29 Apr 2014 01:26:24 GMT

That is the default SAS behaviour as specified.

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

Re: import excel data with formulas

RW9 — Tue, 29 Apr 2014 08:01:10 GMT

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?