title: sas proc sql join causes ERROR: Expression using equals (=) has components that are of different data types
body: <p>SAS EG gives these errors:</p>
  
  <blockquote>
    <p>ERROR: Expression using not equals (^=) has components that are of
    different data types.</p>
    
    <p>ERROR: Expression using equals (=) has components that are of
    different data types.</p>
  </blockquote>
  
  <p>for this line:</p>
  
  <pre><code>on I.app_id = v.appl_id
  </code></pre>
  
  <p><code>proc contents</code> on both fields are <code>Type= Char</code>, <code>Len = 15</code>, <code>Format =$15.00</code>, <code>Informat=$15.00</code>.</p>
  
  <p>I inherited this project and I'm very new to SAS. Any help greatly appreciated.</p>
  
  <pre><code>The CONTENTS Procedure                  
  Data Set Name   TEMP7D.DFS_VER_PANEL    Observations    8953153     
  Alphabetic List of Variables and Attributes                 
  #   Variable    Type    Len Format  Informat
  1   APPL_ID Char    15  $15.00  $15.00 
  2   VER_MAKE_NAME   Char    36  $36.00  $36.00 
  3   VER_MODEL_NAME  Char    50  $50.00  $50.00 
  6   cash_down   Num 8   13.2    13.2
  4   cash_price  Num 8   13.2    13.2
  15  collat_val  Num 8   13.2    13.2
  12  dealer_adds Num 8   13.2    13.2
  16  gap_fee Num 8       
  18  gapi_fee    Num 8       
  17  gapw_fee    Num 8       
  9   net_trade_in    Num 8   13.2    13.2
  8   owing_on_trade  Num 8   13.2    13.2
  5   rebate  Num 8   13.2    13.2
  13  sales_tax_fees  Num 8   13.2    13.2
  19  tot_be_wgap Num 8       
  10  tot_down_pmt    Num 8   13.2    13.2
  14  total_amt_financed  Num 8   13.2    13.2
  7   trade_in    Num 8   13.2    13.2
  11  unpaid_cash_bal Num 8   13.2    13.2
  
  The CONTENTS Procedure                  
  Data Set Name   TEMP7D.INFILE_1 Observations    1852630     
  Alphabetic List of Variables and Attributes                 
  #   Variable    Type    Len Format  Informat
  63  AMT_APP_VER_FINANCED    Num 8   13.2    13.2
  62  AMT_APRV    Num 8   13.2    13.2
  61  AMT_APRV_FINCD  Num 8   13.2    13.2
  65  AMT_APRV_PMT    Num 8   11.2    11.2
  73  AMT_COLLAT  Num 8   13.2    13.2
  41  AMT_DLR_RESV    Num 8       
  66  AMT_INC_TOT Num 8   15.2    15.2
  15  AMT_ORIG_NOTE   Num 8       
  64  AMT_RQSTD   Num 8   13.2    13.2
  11  AMT_TOT_ANN_INC Num 8       
  37  AMT_VER_RATE    Num 8   9.3 9.3
  2   APP_ID  Char    15  $15.00  $15.00 
  14  CDE_ACCT_STAT   Char    2   $2.00   $2.00 
  74  CDE_ACL_NEW_USED    Char    1   $1.00   $1.00 
  75  CDE_ACL_YEAR    Num 8   3   3
  43  CDE_ADJ_USER_CUR    Char    8   $8.00   $8.00 
  29  CDE_CREDIT_ANALYST  Char    6   $6.00   $6.00 
  54  CDE_DIST_PLAN   Num 8       
  44  CDE_LOCATION    Char    6   $6.00   $6.00 
  69  CDE_NEW_USED_COLL   Char    3   $3.00   $3.00 
  27  CDE_OFFICER_APRV    Char    6   $6.00   $6.00 
  28  CDE_OFFICER_CUR Char    6   $6.00   $6.00 
  55  CDE_PAYOFF_CHARGEBACK   Char    3   $3.00   $3.00 
  25  CDE_PROD_APRV   Char    5   $5.00   $5.00 
  26  CDE_PROD_RQSTD  Char    5   $5.00   $5.00 
  70  COLL_MAKE   Char    100 $100.00     $100.00 
  71  COLL_MODEL  Char    100 $100.00     $100.00 
  72  COLL_YR Char    4   $4.00   $4.00 
  110 CRS Num 8       
  56  CURR_UW Char    30  $30.00  $30.00 
  9   CUSTOM_SCORE    Num 8       
  87  CompareVar  Num 8       
  45  DEALER_CODE Char    5   $5.00   $5.00 
  48  DEALER_NAME Char    30  $30.00  $30.00 
  52  DLRACTIVE   Char    1   $1.00   $1.00 
  51  DLRMSA  Char    5   $5.00   $5.00 
  49  DLRSTATE    Char    2   $2.00   $2.00 
  50  DLRZIP  Char    9   $9.00   $9.00 
  13  DLR_NUM_KEY_1   Char    30  $30.00  $30.00 
  60  DRAG_REDUCTION  Num 8       
  40  DTE_ENTERED Num 8   DATETIME20. DATETIME20.
  5   DTE_ORIG_PROCESS    Num 8   DATETIME20. DATETIME20.
  1   DW_ACCT_KEY Num 8       
  93  DlrZipClean Char    5       
  81  FCTR    Num 8       
  20  FTP_RATE    Num 8       
  102 FicoBand    Num 8       
  19  GLAdjBal    Num 8       
  39  ID_PROMOTIONAL  Char    18  $18.00  $18.00 
  42  IND_DLR_FLAT_FEE    Char    1   $1.00   $1.00 
  21  IND_SOLD    Char    1   $1.00   $1.00 
  12  LTV Num 8       
  53  MARKET_REP_NAME Char    50  $50.00  $50.00 
  34  MAXCUSTSCOR Num 8       
  58  NME_DLR_GROUP   Char    50  $50.00  $50.00 
  32  NUM_ACL_MILEAGE Num 8   7   7
  10  NUM_ORIG_TERM   Num 8       
  22  NUM_POOL    Char    4   $4.00   $4.00 
  24  NUM_TERM    Num 8   6   6
  6   ORIGDATEEOM Num 8   DATETIME20. DATETIME20.
  7   ORIG_CR_SCORE   Num 8       
  88  OrigDate    Num 8   DATE9.  
  33  PAYMENT Num 8       
  59  PAYOFF_PERIOD   Num 8       
  35  PCT_APP_RECOMMEND_RATE  Num 8   9.3 9.3
  16  PCT_APR_RTE Num 8   13.7    13.7
  36  PCT_BANK_BUY_RATE   Num 8   9.3 9.3
  30  PCT_BUY_DOWN_RATE   Num 8   8.3 8.3
  31  PCT_DLR_FLAT_FEE_RATE   Num 8   8.3 8.3
  18  PCT_DLR_RATE    Num 8       
  8   PCT_ORIG_DTI    Num 8       
  17  PCT_RATE    Num 8       
  23  PMT_TO_INCOME   Num 8   16.3    16.3
  4   PROD_KEY_2  Char    20  $20.00  $20.00 
  46  REC_RATE    Num 8       
  38  RO_OVERRIDE_CODE    Char    2   $2.00   $2.00 
  3   SRC_SYS_ACCT_KEY    Char    20  $20.00  $20.00 
  68  TFLFlg  Num 8       
  57  TXT_CENTER  Char    15  $15.00  $15.00 
  103 TermBand    Num 8       
  76  VEHICLE_TYPE    Char    120 $120.00     $120.00 
  47  VER_RATE    Num 8   9.3 9.3
  94  VehYr4  Char    7       
  89  Vintage Num 8       
  90  VintageHalf Char    6   $6.00   
  92  VintageMon  Char    7   $7.00   
  91  VintageQtr  Char    6   $6.00   
  95  amt Num 8   10.2    
  107 aprround    Num 8   10.3    
  101 back_end_exc    Num 8       
  106 bkround Num 8   10.3    
  99  cs_exceptions   Num 8       
  85  cust_scr    Num 8       
  105 dlrsplit    Num 8   10.3    
  79  docst_fee   Num 8       
  83  estdrl1 Num 8       
  67  exception   Num 8       
  80  fchg_fee    Num 8       
  100 hd  Char    3       
  108 ltv_PreBE   Num 8   10.2    
  96  new_used    Num 8       
  104 oac Char    1       
  86  orig_cr_scoreAdj    Num 8       
  82  pct_bk_rate Num 8       
  98  price_except    Num 8       
  109 pti_PreBE   Num 8   10.2    
  97  region  Num 8       
  77  segment Char    29      
  84  term    Num 8       
  78  total_backend_fees  Num 8
  </code></pre>
  
  <pre><code>/*  CREATE TABLE TEMP7D.InFile_2 AS*/
          SELECT DISTINCT
              I.*,
              D.pp AS PP_bkround,
              D2.pp AS PP_aprround,
  
              PEL.new AS PelNew,
              PEL.used AS PelUsed,
  
              ELA.Add_ON AS ElaAddOnHist,
  
              ELA_LTV.Factor AS ELAFactor_LTV,
              ELA_OAC.Factor AS ELAFactor_OAC,
              ELA_Term.Factor AS ELAFactor_Term,
  
              ELAF.NEW AS ElafNew,
              ELAF.USED AS ElafUsed,
              ELAL.ADD_ON AS ElalAddOn,
              PCS.Points AS CSPoints,
              PFICO.Points AS FicoPoints,
              PLTV.Points AS LTVPoints,
              PPMT.Points AS PMTPoints,
              PPTI.Points AS PTIPoints,
  
              COALESCE(FIP.FIP,FIP2.FIP) AS FIP,
              COALESCE(FIP.MSA,FIP2.MSA) AS MSA,
  
              ADJ.ELGeoAdj,
  
              CASE
                  WHEN F.src_sys_acct_key NE ''
                      THEN 1
                  ELSE 0
              END AS FraudFlg,
  
              CASE
                  WHEN S.src_sys_acct_key NE ''
                      THEN 1
                  ELSE 0
              END AS SoldFlg,
  
              CASE
                  WHEN S2.src_sys_acct_key NE ''
                      THEN 1
                  ELSE 0
              END AS SecureFlg,
  
              (-1*ND.value) AS Net_Drag_2,
  
              v.ver_make_name,
  
              PD1.Factor AS LTVFactor,
              PD2.Factor AS PTIFactor,
              PD3.Factor As TermFactor,
              EAD.Factor AS EADFactor,
  
              case
                  when year(datepart(dte_entered)) LT 2013
                      then coalesce(C.CreditSeg,4)
                  else coalesce(C.CreditSeg,999) 
              end AS CreditSeg,
  
              case
                  when year(datepart(dte_entered)) LT 2013
                      then coalesce(C.CreditSource,2)
                  else C.CreditSource
              end format CredSrcFmt. As CreditSource
  
          FROM TEMP7D.InFile_1 AS I
  
  /*
      The Sandbox.DFSDrag table was updated in Dec. 2018 with new prepayment 
          rates.  APR was no longer part of the criteria so I set this 
          variable = 9999 to indicate the new prepayment data.  FICO and 
          TERM bands have changed as well.  The previous prepayment data
          is still stored in this table as well and can be identified 
          where APR &lt;&gt; 9999.
  */
          LEFT JOIN Sandbox.DFSDrag AS D
              ON I.New_used = d.New_used
                  AND I.orig_cr_score BETWEEN D.fico_start AND D.fico_end
                  AND I.num_term BETWEEN D.term_start AND D.term_end
                  AND D.apr = 9999
          LEFT JOIN Sandbox.DFSDrag AS D2
              ON I.New_used = d2.New_used
                  AND I.orig_cr_score BETWEEN D2.fico_start AND D2.fico_end
                  AND I.num_term BETWEEN D2.term_start AND D2.term_end
                  AND D2.apr = 9999
  
      /*These two tables were updated based on changes to the EL model.
          The original table was kept (Version = 0).
          Factors effective November 2016 (Version 1)
          Factors effective September 2017 (Version 2)*/
  
          LEFT JOIN Sandbox.DFS_EL_PEL AS PEL
              ON I.dte_entered BETWEEN PEL.el_sm AND PEL.el_em
                  AND COALESCE(I.orig_cr_score, 0) BETWEEN PEL.fsmin AND PEL.fsmax
                  AND COALESCE(I.custom_score, COALESCE(I.cust_scr, 0)) BETWEEN PEL.csmin AND PEL.csmax
                  AND PEL.Version = 2
  
          LEFT JOIN Sandbox.DFS_EL_ELA2 AS ELA
              ON I.dte_entered BETWEEN ELA.el_sm AND ELA.el_em
                  AND ELA.hd = I.hd
                  AND ELA.oac = I.oac
                  AND I.num_term BETWEEN ELA.term_min AND ELA.term_max
                  AND I.ltv BETWEEN ELA.ltv_min AND ELA.ltv_max
                  AND ELA.new_used = I.new_used
                  AND ELA.CRS = I.CRS
                  AND ELA.Version = 2
                  AND datepart(ELA.EL_EM) NE '31Dec9999'd
  
          LEFT JOIN Sandbox.DFS_EL_ELA3 AS ELA_LTV
              ON datepart(I.dte_entered) BETWEEN ELA_LTV.StartDate and ELA_LTV.EndDate
                  AND I.Cust_Scr BETWEEN ELA_LTV.CustScore_Min and ELA_LTV.CustScor_Max
                  AND I.num_term BETWEEN ELA_LTV.Term_Min AND ELA_LTV.Term_Max
                  AND I.LTV BETWEEN ELA_LTV.LTV_Min AND ELA_LTV.LTV_Max
                  AND I.New_Used = ELA_LTV.New_Used
                  AND ELA_LTV.Metric EQ 'LTV'
  
          LEFT JOIN Sandbox.DFS_EL_ELA3 AS ELA_OAC
              ON datepart(I.dte_entered) BETWEEN ELA_OAC.StartDate and ELA_OAC.EndDate
                  AND I.New_Used = ELA_OAC.New_Used
                  AND I.oac = ELA_OAC.oac
                  AND I.Cust_Scr BETWEEN ELA_OAC.CustScore_Min and ELA_OAC.CustScor_Max
                  AND I.num_term BETWEEN ELA_OAC.Term_Min AND ELA_OAC.Term_Max
                  AND I.LTV BETWEEN ELA_OAC.LTV_Min AND ELA_OAC.LTV_Max
                  AND ELA_OAC.Metric EQ 'OAC'
  
          LEFT JOIN Sandbox.DFS_EL_ELA3 AS ELA_Term
              ON datepart(I.dte_entered) BETWEEN ELA_Term.StartDate and ELA_Term.EndDate
                  AND I.New_Used = ELA_Term.New_Used
                  AND I.Cust_Scr BETWEEN ELA_Term.CustScore_Min and ELA_Term.CustScor_Max
                  AND I.num_term BETWEEN ELA_Term.Term_Min AND ELA_Term.Term_Max
                  AND I.LTV BETWEEN ELA_Term.LTV_Min AND ELA_Term.LTV_Max
                  AND ELA_Term.Metric EQ 'Term'
  
          LEFT JOIN Sandbox.dfs_el_adj_factor As ELAF
              ON I.num_term BETWEEN ELAF.term_min AND ELAF.term_max
                  AND I.ltv BETWEEN ELAF.ltv_min AND ELAF.ltv_max 
  
          LEFT JOIN Sandbox.dfs_el_ltv_adj_mar2016 As ELAL
              ON ELAL.new_used = I.new_used
                  AND I.ltv BETWEEN ELAL.ltv_min AND ELAL.ltv_max 
  
          LEFT JOIN Sandbox.DFS_Points PCS 
              ON PCS.JoinVar = COALESCE(I.custom_score, COALESCE(I.cust_scr, 0))
                  AND PCS.PointType EQ 'CustomScorePoints'
          LEFT JOIN Sandbox.DFS_Points PFICO 
              ON PFICO.JoinVar = COALESCE(I.orig_cr_score, 0)
                  AND PFICO.PointType EQ 'FicoPoints'
          LEFT JOIN Sandbox.DFS_Points PLTV
              ON PLTV.JoinVar = INT(I.ltv)
                  AND PLTV.PointType EQ 'LTVPoints'
          LEFT JOIN Sandbox.DFS_Points PPMT 
              ON PPMT.JoinVar = FLOOR(I.payment)
                  AND PPMT.PointType = 'PMTPoints'
          LEFT JOIN Sandbox.DFS_Points PPTI
              ON INT(PPTI.JoinVar*10)/10 = INT(I.pmt_to_income*10)/10
                  AND PPTI.PointType = 'PTIPoints'
  
          LEFT JOIN decan1.zipfip_201904 AS FIP
              ON I.DlrZipClean = FIP.Zip2
          LEFT JOIN (SELECT DISTINCT FIP,MSA 
                      FROM decan1.zipfip_201904) AS FIP2
              ON strip(I.DlrMSA) = strip(FIP2.FIP)
  
          LEFT JOIN Sandbox.DFSELGeoAdjust AS ADJ
              ON DATEPART(I.dte_entered) BETWEEN ADJ.StartDate AND ADJ.EndDate
                  AND I.DlrState = ADJ.DlrState
                  AND COALESCE(FIP.FIP,FIP2.FIP) = ADJ.FIP
  
          LEFT JOIN FraudAccts AS F
              ON I.src_sys_acct_key = F.src_sys_acct_key
  
          LEFT JOIN SoldAccts AS S
              ON I.src_sys_acct_key = S.src_sys_acct_key
  
          LEFT JOIN SecureAccts AS S2
              ON I.src_sys_acct_key = S2.src_sys_acct_key
  
          /*This was added in Dec. 2018 for new drag rates*/
          LEFT JOIN Sandbox.NetDragRates_2018Q3 AS ND
              ON I.num_term BETWEEN ND.num_term_min AND ND.num_term_max
                  AND CASE WHEN I.ind_dlr_flat_fee EQ '' THEN 'N' ELSE I.ind_dlr_flat_fee END = ND.ind_dlr_flat_fee
                  AND CASE WHEN I.ind_dlr_flat_fee IN ('Y','X') THEN 100 
                      WHEN I.ind_dlr_flat_fee EQ 'N' AND I.cde_dist_plan LT 70 THEN 100
                          ELSE I.cde_dist_plan END = ND.cde_dist_plan
  
          LEFT JOIN Sandbox.DFS_PD AS PD1
              ON round(I.ltv_PreBE,0.001) BETWEEN PD1.MetricMin and PD1.MetricMax
              AND I.New_Used = PD1.NewUsed
              AND datepart(I.dte_entered) BETWEEN PD1.StartDate AND PD1.EndDate
              AND PD1.Metric = 'LTV'
  
          LEFT JOIN Sandbox.DFS_PD AS PD2
              on round(I.pti_PreBE,0.001) BETWEEN PD2.MetricMin and PD2.MetricMax
              and I.New_Used = PD2.NewUsed
              and datepart(I.dte_entered) BETWEEN PD2.StartDate AND PD2.EndDate
              and PD2.Metric = 'PTI'
  
          LEFT JOIN Sandbox.DFS_PD AS PD3
              on I.num_term BETWEEN PD3.MetricMin and PD3.MetricMax
              and I.New_Used = PD3.NewUsed
              and datepart(I.dte_entered) BETWEEN PD3.StartDate AND PD3.EndDate
              and PD3.Metric = 'Term'
  
          left join Sandbox.DFS_EAD AS EAD
              on round(I.ltv_PreBE,0.001) BETWEEN EAD.LTVMin and EAD.LTVMax
              and round(I.pti_PreBE,0.001) BETWEEN EAD.PTIMin and EAD.PTIMax
              and I.num_term BETWEEN EAD.TermMin and EAD.TermMax
              and datepart(I.dte_entered) BETWEEN EAD.StartDate AND EAD.EndDate
  
          left join Sandbox.DFS_Credit AS C
              on I.app_id = C.id_appl
  
          left join temp7d.dfs_ver_panel as v
              on I.app_id = v.appl_id
  ;
  QUIT;
  </code></pre>