topic Re: Extracting time series data in SAS Programming

Extracting time series data

Solly7 — Mon, 20 Apr 2020 12:04:13 GMT

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

Re: Extracting time series data

Kurt_Bremser — Mon, 20 Apr 2020 12:18:46 GMT

This:

and lapse_scores(date_received) <= trans_date

makes no sense. Why would you compare a score to a date?

Re: Extracting time series data

PaigeMiller — Mon, 20 Apr 2020 12:19:35 GMT

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"?

Re: Extracting time series data

Solly7 — Mon, 20 Apr 2020 12:21:45 GMT

Hi Kurt, i simply mean date_received<=trans_date

Re: Extracting time series data

Kurt_Bremser — Mon, 20 Apr 2020 12:22:14 GMT

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.

Re: Extracting time series data

Solly7 — Mon, 20 Apr 2020 12:23:53 GMT

Hey Paige Miller, apologies i meant trans_date

Re: Extracting time series data

Jagadishkatam — Mon, 20 Apr 2020 12:25:20 GMT

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;

Re: Extracting time series data

Kurt_Bremser — Mon, 20 Apr 2020 13:28:30 GMT

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;

Re: Extracting time series data

Solly7 — Mon, 20 Apr 2020 14:06:40 GMT

Thanks a lot Kurt..its working perfectly fine