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

Can multiple functions be used in SAS Enterprise Guide Query Builder

PNC — Thu, 03 Aug 2017 15:42:11 GMT

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.

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

tomrvincent — Thu, 03 Aug 2017 16:37:55 GMT

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.

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

ChrisHemedinger — Thu, 03 Aug 2017 17:43:01 GMT

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.

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

PNC — Thu, 17 Aug 2017 21:21:18 GMT

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.