topic Re: Why my left join does not work in SAS Programming

Why my left join does not work

Kayla_Tan222 — Tue, 09 Jul 2019 10:14:22 GMT

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;

Re: Why my left join does not work

euro_andres — Tue, 09 Jul 2019 10:09:14 GMT

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

a.claim=b.claim

and

a.zsumsi=b.totsi

;

QUIT;

Re: Why my left join does not work

gamotte — Tue, 09 Jul 2019 10:13:14 GMT

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.

Re: Why my left join does not work

Tom — Tue, 09 Jul 2019 12:47:41 GMT

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.