topic Re: How to use sub-query result to create new field and set value? in SAS Programming

How to use sub-query result to create new field and set value?

zz — Wed, 28 Sep 2016 13:51:18 GMT

I have a sub-query as follows:

(SELECT D2.DSDECODED FROM DS_02 D2 WHERE D2.DSDECODED ="TREATMENT UNBLINDED" AND D.USUBJID=D2.USUBJID) AS DECODE

How do I program so, instead of DECODE taking the value of D2.DSDECODED(TREATMENT UNBLINDED), it now takes the value of either "YES", when D2.DSDECODED ="TREATMENT UNBLINDED", or

"NO", when D2.DSDECODED ="TREATMENT UNBLINDED" is false

Thank you in advance for your help!

Re: How to use sub-query result to create new field and set value?

JediApprentice — Wed, 28 Sep 2016 15:12:13 GMT

Maybe you could use a CASE statement? Although I'm not sure why you have WHERE D2.DSDECODED ="TREATMENT UNBLINDED". Don't you want to include records that aren't DSDECODED="TREATMENT UNBLINDED". In that case you could do something like this:

(SELECT 
  (CASE
    WHEN D2.DSDECODED="TREATMENT UNBLINDED" THEN "YES"
    ELSE "NO"
  END) AS DECODE
FROM DS_02 D2 WHERE D.USUBJID=D2.USUBJID)

Re: How to use sub-query result to create new field and set value?

zz — Wed, 28 Sep 2016 17:16:41 GMT

Hi JediApprentice,

Thank you for the below suggestion! The logic worked the way you have it.

In terms of condition WHERE D2.DSDECODED ="TREATMENT UNBLINDED", if I have it, the code works to only set "YES" for the "DECODE" Flag, if removed, it evaluates to more than one row, breaking the code.