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;
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;
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.
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.
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!
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.
Ready to level-up your skills? Choose your own adventure.