Hi i need help, i have two table below, 1 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
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
2 01Jan2019 300 1 30feb2019 65 580
1 01Feb2019 500 2 01Jan2019 10 700
2 01Feb2019 300
Data Want
policy_no trans_date amount date_received lapse_score itc_score
1 01Jan2019 500 01Dec2018 50 500
2 01Jan2019 300 01Jan2019 10 700
1 01Feb2019 500 01Dec2018 50 500
2 01Feb2019 300 01Jan2019 10 700
Hi @Solly7
Here is a way to achieve this, using PROC SQL
data table1;
input policy_no trans_date:date9. amount;
format trans_date date9.;
datalines;
1 01Jan2019 500
2 01Jan2019 300
1 01Feb2019 500
2 01Feb2019 300
;
run;
data table2; /*30feb2019 have been replaced by 28feb2019*/
input policy_no date_received:date9. lapse_score itc_score;
format date_received date9.;
datalines;
1 01Dec2018 50 500
1 28feb2019 65 580
2 01Jan2019 10 700
;
run;
proc sql;
create table want as
select a.*, b.date_received, b.lapse_score, b.itc_score
from table1 as a left join table2 as b
on a.policy_no =b.policy_no and b.date_received <= a.trans_date
order by trans_date, policy_no;
quit;
please try the below code
data have1;
input policy_no trans_date:date9. amount;
cards;
1 01Jan2019 500
2 01Jan2019 300
1 01Feb2019 500
2 01Feb2019 300
;
data have2;
input policy_no date_received:date9. lapse_score itc_score;
cards;
1 01Dec2018 50 500
1 28feb2019 65 580
2 01Jan2019 10 700
;
proc sql;
create table want as select a.*,b.date_received, b.lapse_score,b.itc_score from have1 as a left join have2 as b on a.policy_no=b.policy_no and b.date_received<=a.trans_date;
quit;
Hi @Solly7
Here is a way to achieve this, using PROC SQL
data table1;
input policy_no trans_date:date9. amount;
format trans_date date9.;
datalines;
1 01Jan2019 500
2 01Jan2019 300
1 01Feb2019 500
2 01Feb2019 300
;
run;
data table2; /*30feb2019 have been replaced by 28feb2019*/
input policy_no date_received:date9. lapse_score itc_score;
format date_received date9.;
datalines;
1 01Dec2018 50 500
1 28feb2019 65 580
2 01Jan2019 10 700
;
run;
proc sql;
create table want as
select a.*, b.date_received, b.lapse_score, b.itc_score
from table1 as a left join table2 as b
on a.policy_no =b.policy_no and b.date_received <= a.trans_date
order by trans_date, policy_no;
quit;
thanks a lot!! it works
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.