topic Re: Enterprise Guide Help with Functions in a Query Builder in SAS Programming

Enterprise Guide Help with Functions in a Query Builder

dude12345 — Tue, 13 Aug 2013 15:49:34 GMT

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!

Re: Enterprise Guide Help with Functions in a Query Builder

Fugue — Tue, 13 Aug 2013 20:24:50 GMT

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

Re: Enterprise Guide Help with Functions in a Query Builder

Reeza — Tue, 13 Aug 2013 20:34:18 GMT

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)

Re: Enterprise Guide Help with Functions in a Query Builder

Fugue — Tue, 13 Aug 2013 20:48:58 GMT

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

Re: Enterprise Guide Help with Functions in a Query Builder

dude12345 — Tue, 13 Aug 2013 21:09:58 GMT

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.

Re: Enterprise Guide Help with Functions in a Query Builder

RichardinOz — Thu, 15 Aug 2013 00:53:50 GMT

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