import excel data with formulas

Reply
New Contributor
Posts: 3

import excel data with formulas

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 Smiley Happy

Super User
Super User
Posts: 7,997

Re: import excel data with formulas

Posted in reply to Sophie_12

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?

Super User
Posts: 7,866

Re: import excel data with formulas

Posted in reply to Sophie_12

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
Super Contributor
Posts: 644

Re: import excel data with formulas

Posted in reply to Sophie_12

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

New Contributor
Posts: 3

Re: import excel data with formulas

Posted in reply to RichardinOz

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

Super User
Posts: 19,875

Re: import excel data with formulas

Posted in reply to Sophie_12

That is the default SAS behaviour as specified.

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

Super User
Super User
Posts: 7,997

Re: import excel data with formulas

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?

Ask a Question
Discussion stats
  • 6 replies
  • 3128 views
  • 0 likes
  • 5 in conversation