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
"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;
thanks you RW9, i will try it
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;
thanks !
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.