Excel Formulas into SAS?

[dhunited12]

Hello!

I just recently began exploring SAS. I've learned through some tutorials, but one thing I am unable to find is how to enter specific Excel Formulas into SAS Enterprise Guide. 

For example, how would I enter:

IFERROR(1 - [Cheese]/[Bread], 0) 

***Given that I have a column labeled cheese and bread with numbers in each row***

Thank you in advance 🙂 

[RW9]

You do not enter "Excel formulas" into SAS.  SAS is a complete data and dataprocessing suite with its own ways of working and programming language called base SAS.  Excel is a completely different product by a different company, and works differently in many ways to SAS.  You would re-code your "formula" using SAS syntax, so something like:

data want;
  set have;
  want_var=ifn(cheese/bread=.,0,cheese/bread);
run;

[jebjur]

Can I ask what you wish to accomplish in Enterprise Guide? You wouldn't enter specific Excel formulas into Eguide, but instead you can read your Excel file into Eguide, create a SAS data set, and then you can manipulate the data in the data set with SAS functions. 

[ballardw]

Reference variable by the name no () no [] no {}. Use () to control order of operations.

However do not expect us to translate Excel code directly. It is better to indicate what you are attempting as some of the things Excel does across rows will be extremely different in SAS and frankly many of us do not want to go into Excel documentation to find out what a function does.

The data step generally examines/uses/processes data one observation (SAS terminology) or row at a time.

The approach for ALL calculations in SAS is: targetvariablename = <some operations on one or more variables or values>;

So part of what you are doing is 

   1-(cheese/bread).

If your "iferror" is because you do not know that bread might be 0 causing an improper division then the better SAS approach is

If bread >0 then value = 1-(cheese/bread);

else 0;

(or what ever expected range of values Bread should have to make a calculation)

One reason for different user logic is the existence of the SAS special value MISSING.  Many functions and operations with data in SAS will simply return missing without an error. Other functions are intended to work with missing to create an expected value ignoring the missing values. Example: the SUM function in SAS will add the values of variable that are present and ignore any variables with missing values. Example the MIN or MAX function will return the minimum or maximum non-missing value of the variables or values presented.

[TomKari]

You have several options. As it appears you're new to this environment, I'll discuss a non-programming option.

Enterprise Guide is a "point and click" environment intended to make accomplishing results with the SAS facilities easier for non-SAS programmers (like you!)

One way to do what you describe is with the Query Builder. Assume you have a SAS dataset with variables Cheese and Bread. In the Query Builder, you would create a new advanced expression, which would be very similar to your Excel formula:

1 - (Cheese/Bread)

But when you run it, you'll notice that any rows where Bread is 0 have a . as a result. The "." is a missing value in SAS, and among other things is the result of a divide by zero.

So to take it the long way, you could create a new query, and add a new advanced expression. This time use a function called IFN, which returns values base on whether your original value is true, false, or missing. So it's very close to your IFERROR function. Assume you called your first variable RESULT, your expression looks like:

IFN(RESULT, RESULT, RESULT, 0)

and when you run it, you'll see all of the "." values are converted to zero.

Of course, you're probably ahead of me, you can combine the two operations into one advanced expression as you did in Excel.

IFN(1 - (Cheese/Bread), 1 - (Cheese/Bread), 1 - (Cheese/Bread), 0)

will do the whole thing.

The discussions about SAS syntax are a different way of approaching it, using the SAS language in a program editor window. As your skills with SAS increase, you'll move into that method of solving particular problems, but for now I suggest you try to stay with the non-programming options.

Tom

[Reeza]

You're in EG, so I'm assuming you're using Query Builder. 

The code you've shown is an IF then statement which translates to a CASE statement in the query builder. 

Add a computed column, click advanced and type in a case statement. 

case when (1-cheese/bread) = . then 0
else 1-cheese/bread end