Join SAS tables with condition

[Mirou]

Hello,

i need to join two sas tables with condition,

The first tables contains a list of contract with two dates : start date and expiration date, and also the premium.

the second table contains claims with occurence date, amounts and contract.

i have to join those tables by contract, the occurence date have to be between start date and expiration date.

A contract may have many claims, i need to sum the amounts of claims and affected to the contract .

My main problem is how to join those tables and to avoid duplicates (premium)

i Tried to use the SQL procedure, right join/full join. but it doesn't work,

also  i tried the merge procedure, 

Thanks a Lot

[RW9]

"i Tried to use the SQL procedure, right join/full join. but it doesn't work, also  i tried the merge procedure, "  - this does not tell us anything.  Posting test data in the form of a datastep, what the output should look like, and what you have done will.  

I would say SQL is likely the simplest option:

proc sql;
  create table want as
  select  a.contract,
          a.start_date,
          a.end_date,
          sum(b.amount) as amount
  from    first_table a
  left join second_table b
  on      a.contract=b.contract
  and     a.start_date <= b.occurence_date <= a.end_date
  group by a.contract,
           a.start_date,
           b.end_date;
quit;

[Mirou]

thanks you RW9, i will try it 

[Kurt_Bremser]

Look if this suits you:

data contracts;
input contract_id $ premium start_date :yymmdd10. end_date :yymmdd10.;
format start_date end_date yymmddd10.;
cards;
C1 100 2016-01-01 2016-12-31
C1 110 2017-01-01 2017-12-31
;
run;

data claims;
input contract_id $ amount occur_date :yymmdd10.;
format occur_date yymmddd10.;
cards;
C1 50 2016-03-05
C1 20 2016-09-01
C1 30 2017-06-29
;
run;

proc sql;
create table want as
select
  a.contract_id,
  a.start_date,
  a.end_date,
  sum(b.amount) as amount
from
  contracts a
  left join
  claims b
  on (a.contract_id=b.contract_id and a.start_date <= b.occur_date <= a.end_date)
group by
  a.contract_id,a.start_date,a.end_date
;
quit;

[Mirou]

thanks !