want to output Y if observations variable x equals to something

[jmoore168]

Hi

I want to output "Y" if FIN_CD equals "1S". If there's any other variable other than 1S then I do not want to output them. I'm not sure if I'm able to do this in the WHERE statement or if I need a data step.

Here is some sample code:

PROC SQL;
create table automation_exception as

SELECT  
  TRANSACTION_FACT.TRN_CD as FIN_CD,
       
FROM
    RDWP0.TRANSACTION_FACT,
    RDWP0.TRANSACTION_CD_LOOKUP,

WHERE
    (TRANSACTION_CD_LOOKUP.TRN_CD=TRANSACTION_FACT.TRN_CD)
    AND (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID);

QUIT;
    

[ballardw]

---

@jmoore168 wrote:

Hi

I want to output "Y" if FIN_CD equals "1S". If there's any other variable other than 1S then I do not want to output them. I'm not sure if I'm able to do this in the WHERE statement or if I need a data step.

---

Did you mean if FIN_CD is not equal to '1S' you don't want them in the output at all? If so subset the data as early as practical:

FROM

(select * from RDWP0.TRANSACTION_FACT where TRN_CD = '1S') ,

RDWP0.TRANSACTION_CD_LOOKUP,

will only select the records of interest before combining with the other data set.

[jmoore168]

Hi Ballardw

Your solution worked perfectly. However I may have been mistaken with regard to how I want to handle other FIN_CD's separately from 1S. For example if FIN_CD output's anything other than '1S' then I want it to output blank or 'N'. This blank tells us we need to take a specific action. 1S means we have already did it. Basically this is an exception report. I need to combine the previous code you sugguested in addition to what I'm asking here.

Hope this helps.

[DanielSantos · Accepted solution]

Hi.

Could it be as simple as the following?

SELECT  
  case when TRANSACTION_FACT.TRN_CD eq '1S' then '1S' else 'N' end as FIN_CD,
       
  

Daniel Santos @ www.cgd.pt