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

[zz]

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!

[JediApprentice · Accepted solution]

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)

[zz]

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.