Enterprise Guide Help with Functions in a Query Builder

[dude12345]

I need to add a new column to a data set that would sort of be an if - then statement in excel how I know it.

I have a propensity column that calculates the propensity of certain diseases among age and gender groups.

What I want is to add a new column that returns a 1 if the propensity is greater than 0, and returns a 0 if propensity is 0.

Can anyone help me?

I saw A LOT of functions in the query builder "New Computed Column" --> "Advanced Expression" field. But I am not sure if that is it, I was originally thinking I would need to do a re coded column, but again I do not know for sure.

Any help would be appreciated.

THANKS!

[Fugue]

You are definitely on the right track.

In EG 4.2 http://support.sas.com/kb/38/261.html

In EG 4.1 http://support.sas.com/kb/32/160.html

[Reeza · Accepted solution]

There is more than one way.

A Case statement would also work in advanced expression. Its more like an if-then statement.

Case when propensity>0 then 1

          when propensity=0 then 0

          else -1

END

Remember to rename and re-alias the column in the dialog.

Here's a SQL reference to CASE statement.

CASE (Transact-SQL)

[Fugue]

True true, the Case statement sitll works via "advanced expression". For some reason, SAS is promoting "if-then-else" logic via the "recode column" function. Both the "advanced expression" and the "recode column" features result in a CASE expression (when used for if-then-else logic).

[dude12345]

Thanks everyone! I am not at work anymore (and thus do not have access to our virtual machine running SAS)

I will try these out tomorrow.

[RichardinOz]

Another option to bear in mind for true/false conditions is the use of a function instead of case statements.

In this instance the sign() function would provide the result you require and is more efficient and less work than the case solution.

Richard