Desktop productivity for business analysts and programmers

Can multiple functions be used in SAS Enterprise Guide Query Builder

Reply
Occasional Contributor PNC
Occasional Contributor
Posts: 5

Can multiple functions be used in SAS Enterprise Guide Query Builder

I have a table of data and need to use one calculation for one region and a different calculation for the other regions.  The calculations would include other columns of data.  Is it possible to combine functions into one calculated column.

 

Data includes Region and Current Balance. 

 

This is what I am trying to accomplish:

 

If the Region = X and the Current Balance is <100,000 then the end result is a flat $200. For Region X, if the Current Balance is >100,000 the end result is Current Balance*.0025. For all other Regions if the Current Balance is <100,000 then the end result is a flat $500 or if the Current Balance is >100,000 then the end result is Current Balance*.005.

 

How do I accomplish this? I am not a programmer and need to be able to do this within the Query Builder.  Thank you.

Contributor
Posts: 33

Re: Can multiple functions be used in SAS Enterprise Guide Query Builder

You'd have to do it using the advanced expression option.  It kind of walks you thru it a bit, in that the functions are all listed as well as their parameters.

Community Manager
Posts: 2,955

Re: Can multiple functions be used in SAS Enterprise Guide Query Builder

[ Edited ]

In your case, you'd want the CASE statement in your Advanced expression.  Something like:

 

case
 when region="X" and balance<100000 then 200
 when region="X" and balance>=100000 then balance*.0025
 when region ^="X" and balance<100000 then 500
 else balance*.005
end /* as NewValue */ 

The expression builder will add the "as NewValue" bit on for you, but you'll have to pretty much enter the rest.  You can try the Recode Values option instead -- it might give you some point and click methods for this.  The end result would still be a CASE clause.

Occasional Contributor PNC
Occasional Contributor
Posts: 5

Re: Can multiple functions be used in SAS Enterprise Guide Query Builder

Posted in reply to ChrisHemedinger

Thank you!  I haven't had a chance to see if this will work yet, but I believe it will do what I need.  I appreciate the help.

Ask a Question
Discussion stats
  • 3 replies
  • 167 views
  • 0 likes
  • 3 in conversation