Creating a flag within PROC SQL

guest1994 — Mon, 11 May 2020 13:25:51 GMT

I have this code below, and I'm trying to create a flag (MTH6=1) to indicate if an account exists within OUT_EXT.IAS_L2BS_&MTH6.

What would you suggest I do to my code, I've highlighted this with comments?

PROC SQL;
CREATE TABLE SPDSWORK.CARDS_ACCOUNT_DER AS
SELECT A.KEY_ACCT
,A.KEY_ORG 
,A.DATE_CLOSED
,A.DATE_OPENED
,A.LOGO
,A.DATE_LAST_CYCLE
,A.FI_DTE_LAST_C_INCR

,A.CRLIM AS CRLIM0
,B.CRLIM AS CRLIM6
,A.CURR_BAL AS CURR_BAL0
,B.CURR_BAL AS CURR_BAL6
,A.PMT_CYCLE_DUE AS PMT_CYCLE_DUE0
,B.PMT_CYCLE_DUE AS PMT_CYCLE_DUE6
,A.BLOCK_CODE_1 AS BLOCK_CODE_1_0
,B.BLOCK_CODE_1 AS BLOCK_CODE_1_6
,A.BLOCK_CODE_2 AS BLOCK_CODE_2_0
,B.BLOCK_CODE_2 AS BLOCK_CODE_2_6

,C.BT_FLAG0 AS BB_BT_FLAG0
,C.MT_FLAG0 AS BB_MT_FLAG0
,C.BT_FLAG6 AS BB_BT_FLAG6
,C.MT_FLAG6 AS BB_MT_FLAG6
,C.BT_BEHAVIOUR_SCORE6
,C.MT_BEHAVIOUR_SCORE6
,C.BT_CII0
,C.MT_CII0
,C.BT_CDI0
,C.MT_CDI0
,C.BT_DSR0
,C.MT_DSR0
,C.CONTRACT_RATE_IND

,D.MT_FLAG0 AS NB_MT_FLAG0
,D.BT_FLAG6 AS NB_BT_FLAG6
,D.MT_FLAG6 AS NB_MT_FLAG6

/*Combine Front Book and Back Book MT amounts*/
,SUM(C.MT_AMT,D.MT_AMT) AS MT_AMT

,E.CFB_NEW_LIMIT_01
,E.CFB_OLD_LIMIT_01
,E.CO_LENGTH

,F.CII
,F.DCM
,F.KEY_41
,F.BEHAVIOUR_SCORE
,F.KEY_469
,F.KEY_710
,F.KEY_786
,F.KEY_787
,F.KEY_860
,F.KEY_980
,F.KEY_981

,G.KEY_41 AS CYCLES_DELINQUENT6
,G.BEHAVIOUR_SCORE AS CARS16

,H.FH_AMT_PMTS_01
,H.FH_END_STMT_BAl_02

FROM OUT_EXT.IAS_L2BS_&MTH0. 		A
/*CREATE MTH6 INDICATOR*/
LEFT JOIN OUT_EXT.IAS_L2BS_&MTH6. 	B
ON A.KEY_ACCT = B.KEY_ACCT
LEFT JOIN SPDSWORK.BALXFER_BB_DERV	C
ON A.KEY_ACCT = C.KEY_ACCT
LEFT JOIN SPDSWORK.BALXFER_NB_DERV	D
ON A.KEY_ACCT = D.KEY_ACCT
LEFT JOIN OUT_EXT.TRDRCOCR_&MTH0.	E
ON A.KEY_ACCT = E.KEY_ACCT
LEFT JOIN OUT_EXT.TRDRKEYS_&MTH0.	F
ON A.KEY_ACCT = F.HDR_ACCOUNT_ID
LEFT JOIN OUT_EXT.TRDRKEYS_&MTH6.	G
ON A.KEY_ACCT = G.HDR_ACCOUNT_ID
LEFT JOIN OUT_EXT.IAS_L2HB_&MTH0.	H
ON A.KEY_ACCT = H.KEY_ACCT
;
QUIT;

Re: Creating a flag within PROC SQL

PaigeMiller — Mon, 11 May 2020 13:34:14 GMT

Show us a brief example. We don't know what you are trying to do.

Re: Creating a flag within PROC SQL

Kurt_Bremser — Mon, 11 May 2020 13:34:52 GMT

Add

case
  when not missing(b.key_acct)
  then 1
  else 0
end as mth6

to your select.

topic Re: Creating a flag within PROC SQL in SAS Programming

Creating a flag within PROC SQL

Re: Creating a flag within PROC SQL

Re: Creating a flag within PROC SQL