Enterprise Guide Help with Functions in a Query Builder

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Enterprise Guide Help with Functions in a Query Builder

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!


Accepted Solutions
Solution
‎08-13-2013 04:34 PM
Super User
Posts: 19,789

Re: Enterprise Guide Help with Functions in a Query Builder

Posted in reply to dude12345

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)

View solution in original post


All Replies
Super Contributor
Posts: 307

Re: Enterprise Guide Help with Functions in a Query Builder

Posted in reply to dude12345

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

Solution
‎08-13-2013 04:34 PM
Super User
Posts: 19,789

Re: Enterprise Guide Help with Functions in a Query Builder

Posted in reply to dude12345

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)

Super Contributor
Posts: 307

Re: Enterprise Guide Help with Functions in a Query Builder

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).

Occasional Contributor
Posts: 7

Re: Enterprise Guide Help with Functions in a Query Builder

Posted in reply to 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.

Super Contributor
Posts: 644

Re: Enterprise Guide Help with Functions in a Query Builder

Posted in reply to dude12345

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 670 views
  • 6 likes
  • 4 in conversation