BookmarkSubscribeRSS Feed
dhunited12
Calcite | Level 5

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 🙂 

 

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Employee

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
Super User

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
Onyx | Level 15

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2131 views
  • 5 likes
  • 6 in conversation