Why my left join does not work

[Kayla_Tan222]

Hi all, facing problem regarding the left join.

I want the ratecde column from pl201903.Fire_clm_cov_201903 added into the table pl201903.Fire_clm_201903.

When the new column is added, both claim datas from two table sources must be the same.

and also the zsumsi data from pl201903.Fire_clm_201903 must be the same as the totsi from the second one pl201903.Fire_clm_cov_201903. 

Here's my code below, However, this does not work. I don't know where is it wrong. It will automatically help me add on some rows which is repeat from the existing rows from original file.

proc sql noprint;

create table pl201903.query as

select a.*,b.ratecde as ratecde_1 from pl201903.Fire_clm_201903 a left join pl201903.Fire_clm_cov_201903 b

on a.claim=b.claim and a.zsumsi=b.totsi;

run;

[euro_andres]

Hello It seems that the reference to your second table is incorrect. Also the Left join syntax is incorrect. Please see below:

proc sql noprint;

    create table pl201903.query as

    select 

      a.*,

      b.ratecde AS ratecde_1

    from 

      pl201903.Fire_clm_201903 a

    left join 

      pl201903.Fire_clm_cov_201903 b 

    on 

      a.claim=b.claim 

      and 

      a.zsumsi=b.totsi

    ;

QUIT;

[gamotte]

Hello,

Show us some example data, the results you obtain and those you want. A log extract can also help.

In your code, you wrote b*ratecde instead of b.ratecde.

[Tom]

It is not at all clear what your issue is.

Perhaps the issue is that your B table has repeating observations?

So if LEFT has one observation of a particular key combination but RIGHT has 10 observations for that same key combination the result of your left join will have 10 observations for that key combination.

You need to figure out a way to make the right side have just one observation.  This might work:

create table pl201903.query as
  select a.*,b.ratecde as ratecde_1 
  from pl201903.Fire_clm_201903 a 
  left join (select distinct claim,totsi,ratecde from pl201903.Fire_clm_cov_201903) b
    on a.claim=b.claim and a.zsumsi=b.totsi
;

 But you might need to use some method to pick one of many different RATECDE values.