Below is the query. /* clear syscc before new query */ %let syscc=0; %let sysrc=0; options Locale=en_AU; LIBNAME TDBUSCMP TERADATA SERVER="voda2.vodafone.com.au" SCHEMA=AUPR_BUS_VIEW USER=svc_pr_sasci_0010 PASSWORD=******** ; libname MATables list; PROC SQL; Create table MATables.TEDAGM4IWAVGOTPUCjohnm as SELECT DISTINCT table2.SUBS_ID AS SUBS_ID LABEL='.Subscriber Id' FORMAT=F9.0, ( table3.Acct_Id ) AS ACCT_ID LABEL='Account Id' FORMAT=F9.0, ( table4.Acct_Num ) AS ACCT_NUM_1 LABEL='Account Number' FORMAT=$50., ( table3.Accs_Num ) AS ACCS_NUM_3 LABEL='Access Number' FORMAT=$50., ( table3.Email_Bill_Addr ) AS EMAIL_BILL_ADDR LABEL='Billing Email Address' FORMAT=$100., ( table3.Mktg_Prmssn_EMail_Ind ) AS MKTG_PRMSSN_EMAIL_IND_1 LABEL='Marketing Permission Email Indicator' FORMAT=$1., ( table3.Mktg_Prmssn_Acct_Ind ) AS MKTG_PRMSSN_ACCT_IND_1 LABEL='Account Marketing Permission Indicator' FORMAT=$1., ( table3.Mktg_Prmssn_Ind ) AS MKTG_PRMSSN_IND LABEL='Marketing Permission Indicator' FORMAT=$1., ( table3.Acct_Type_Cd ) AS ACCT_TYPE_CD LABEL='Account Type Code' FORMAT=$30., Page 4 of 7 ( CASE WHEN table3.Accs_Num NOT LIKE '614%' THEN 'Fixed' WHEN table3.Plan_Type_Cd = 'MBB' THEN 'MBB' ELSE 'Voice' END ) AS SERVICE_TYPE_CODE LABEL='Service Type Code' FORMAT=$30., ( table5.Cntct_Title_Cd ) AS CNTCT_TITLE_CD_1 LABEL='Admin Contact Title' FORMAT=$30., ( CASE UPCASE(table5.Frst_Nm) WHEN 'N/A' THEN '' WHEN 'NA' THEN '' ELSE table5.Frst_Nm END ) AS ADMIN_CONTACT_FIRST_NAME_2 LABEL='Admin Contact First Name (N/A Removed)' FORMAT=$100., ( table5.Last_Nm ) AS LAST_NM_1 LABEL='Admin Contact Last Name' FORMAT=$100., ( table3.Addr_Ln_One_Txt ) AS ADDR_LN_ONE_TXT LABEL='Billing Account Address Line One' FORMAT=$1000., ( table3.Addr_Ln_Two_Txt ) AS ADDR_LN_TWO_TXT_2 LABEL='Billing Account Address Line Two' FORMAT=$1000., ( table3.Sbrb_Nm ) AS SBRB_NM LABEL='Billing Account Address Suburb' FORMAT=$100., ( table3.Regn_Cd ) AS REGN_CD_3 LABEL='Billing Account Address State' FORMAT=$30., ( table3.Pstl_Cd ) AS PSTL_CD LABEL='Billing Account Address Postcode' FORMAT=$30., ( table3.Curr_Mnfctr_Nm ) AS CURR_MNFCTR_NM LABEL='In Use Device Manufacturer' FORMAT=$1000., ( table3.Curr_Dvc_Gnrtn ) AS CURR_DVC_GNRTN LABEL='Current Handset Type Code' FORMAT=$30., ( trim(left(put(table3.Prim_Prod_Key, best32.))) ) AS PRIMARY_PRODUCT_KEY LABEL='Primary Product Key' FORMAT=$50., ( table3.Curr_Dvc_Rollup_Nm ) AS CURR_DVC_ROLLUP_NM LABEL='In Use Device Model Rollup' FORMAT=$1000., ( table3.Plan_Cntrct_Tnr_Mnths ) AS PLAN_CNTRCT_TNR_MNTHS LABEL='Subscriber Contract Tenure' FORMAT=BEST12., ( table3.Subs_Tnr_Mnths ) AS SUBS_TNR_MNTHS_1 LABEL='Subscription Tenure (Month)' FORMAT=F11.0 FROM ( SELECT DISTINCT ( table0.Subs_Id ) AS SUBS_ID LABEL='Subscriber Id' FORMAT=F9.0 FROM MATables.TGHGEU2VUBJBDUVX2johnm table1 Inner join TDBUSCMP.Cmpst_Actv_Subs_Curr table0 on table1.SUBS_ID = table0.Subs_Id ) table2 Left join TDBUSCMP.Cmpst_Actv_Subs_Curr table3 on table2.SUBS_ID = table3.Subs_Id Left join TDBUSCMP.Cmpst_Acct_Curr table4 on ( ( table3.Acct_Id =table4.Acct_Id ) ) Left join TDBUSCMP.Dim_Party table5 on ( table5.Party_Id=table3.Acct_Party_Id ) ; quit;
... View more