BookmarkSubscribeRSS Feed
Kayla_Tan222
Calcite | Level 5

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;

3 REPLIES 3
euro_andres
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 596 views
  • 0 likes
  • 4 in conversation