Hi i need help, i have two table below, table1 is payment_transactions( consisting of all collections transactions), and the other table is ITC_data(consisting of credit score and lapse score). all i need is every record from payment_transactions table with itc_score and lapse_scores(date_received) <= trans_date for each record. If particular policy number have two received dates<=action_date, i need to take one(latest record) to avoid duplicates
Data Have
table1(payment_transactions table) table2(ITC_data)
policy_no trans_date amount policy_no date_received lapse_score itc_score
1 01Jan2019 500 1 01Dec2018 50 500
1 01Feb2019 500 2 01Jan2019 10 700
2 30Jan2019 300 2 15Jan2019 30 610
2 30Feb2019 300
Data Want
policy_no trans_date amount date_received lapse_score itc_score
1 01Jan2019 500 01Dec2018 50 500
1 01Feb2019 500 01Dec2018 50 500
2 30Jan2019 300 15Jan2019 30 610
2 30Feb2019 300 15Jan2019 30 610
Everything in one SQL:
proc sql;
create table want as
select
t1.policy_no,
t1.trans_date,
t1.amount,
t2.date_received as date_received,
t2.lapse_score,
t2.itc_score
from table1 t1 left join table2 t2
on t1.policy_no = t2.policy_no and t2.date_received <= t1.trans_date
group by t1.policy_no, t1.trans_date
having t2.date_received = max(t2.date_received)
;
quit;
This:
and lapse_scores(date_received) <= trans_date
makes no sense. Why would you compare a score to a date?
UNTESTED CODE
proc sql;
create table want as select table1.*,table2.date_received,table2.lapse_score,
table2.itc_score
from table1 left join table2
on table1.policy_no=table2.policy_no
group by table2.policy_no
having table2.date_received = max(table2.date_received);
quit;
What do you mean by "action_date"?
And you won't find 30Feb2019 in any calendar I know of.
Please supply example data in WORKING(!!) data steps with datalines, as by doing this you yourself can find such mistakes on your own.
please try the below code
data have1;
input policy_no trans_date:date9. amount;
format trans_date date9.;
cards;
1 01Jan2019 500
1 01Feb2019 500
2 30Jan2019 300
2 28Feb2019 300
;
data have2;
input policy_no date_received:Date9. lapse_score itc_score;
format date_received date9.;
cards;
1 01Dec2018 50 500
2 01Jan2019 10 700
2 15Jan2019 30 610
;
proc sql;
create table want as select b.*, a.trans_date, a.amount from have1 as a left join have2 as b on a.policy_no=b.policy_no and b.date_received<= a.trans_date
order by b.policy_no, a.trans_date,b.date_received;
quit;
data want2;
set want;
by policy_no trans_date;
if last.trans_date;
run;
Everything in one SQL:
proc sql;
create table want as
select
t1.policy_no,
t1.trans_date,
t1.amount,
t2.date_received as date_received,
t2.lapse_score,
t2.itc_score
from table1 t1 left join table2 t2
on t1.policy_no = t2.policy_no and t2.date_received <= t1.trans_date
group by t1.policy_no, t1.trans_date
having t2.date_received = max(t2.date_received)
;
quit;
Thanks a lot Kurt..its working perfectly fine
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.