topic Re: Time Series data in SAS Programming

Time Series data

Solly7 — Tue, 14 Apr 2020 07:54:07 GMT

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

Re: Time Series data

Jagadishkatam — Tue, 14 Apr 2020 08:11:46 GMT

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;

Re: Time Series data

ed_sas_member — Tue, 14 Apr 2020 08:20:44 GMT

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;

Re: Time Series data

Solly7 — Tue, 14 Apr 2020 14:19:24 GMT

thanks a lot!! it works