Extracting time series data

[Solly7]

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

[Kurt_Bremser]

This:

and lapse_scores(date_received) <= trans_date

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

[Solly7]

Hi Kurt, i simply mean date_received<=trans_date

[PaigeMiller]

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

[Solly7]

Hey Paige Miller, apologies i meant trans_date

[Kurt_Bremser]

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.

[Jagadishkatam]

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;

[Kurt_Bremser · Accepted solution]

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;

[Solly7]

Thanks a lot Kurt..its working perfectly fine