topic Re: How to fetch total pay off based on due date in New SAS User

How to fetch total pay off based on due date

Trishjais — Wed, 20 May 2020 16:39:47 GMT

Hi Experts

I have a customer base table which has due date of each customer like below.

cust_id	due_dt
1	4/2/2020
2	4/9/2020
3	4/17/2020
4	4/28/2020
18	4/30/2020
24	4/21/2020

I also have several tables which are being generated on daily basis. Like Delinquency amount table, outstanding balance table etc.

Table names are:

_yyyymmddDel_amt and _yyyymmdd_Os_Amt

e.g. for April month I have 30*2 tables.

MY desired output is as below:

How Can I achieve this dynamically, without having the need of appending all the daily tables to form a monthly table due to space and run time constraint.

cust_id	due_dt	Del_amt	Os_Amt
1	4/2/2020	Del_amt from 20200402Del_amt table	Os_amt from 20200402Del_amt table
2	4/9/2020	Del_amt from 20200409Del_amt table	Os_amt from 20200409Os_amt table
3	4/17/2020	Del_amt from 20200417Del_amt table	Os_amt from 20200417Os_amt table
4	4/28/2020	Del_amt from 20200428Del_amt table	Os_amt from 20200428Os_amt table
18	4/30/2020	Del_amt from 20200430Del_amt table	Os_amt from 20200430Os_amt table
24	4/21/2020	Del_amt from 20200421Del_amt table	Os_amt from 20200421Os_amt table

Re: How to fetch total pay off based on due date

Reeza — Wed, 20 May 2020 17:19:28 GMT

How big do you expect your customer_base table to be?
Are you looking for speed efficiency to get the output or to program it.

I'd probably write a script that looped through and grabbed each data and appended it into your data set.

%macro insert(ID=, date=);
%let date_table = %sysfunc(putn(&date, yymmdd10.));
insert into tableName1
select del_amt from _&date_table.DEL_amt;
insert into tableName2
select os_amt from _&date_table.DEL_amt;
%mend;

Then call that macro for each line in your data set using CALL EXECUTE().

There may be more efficient methods but this would definitely work. The code above is just to give you an idea, it's probably not even correct syntax wise.

Re: How to fetch total pay off based on due date

Trishjais — Wed, 20 May 2020 17:28:52 GMT

Thanks Reeza,
Thanks for your input.
My base table has approx 13 million records, I was looking for an optimized way of achieving this.
Thanks

Re: How to fetch total pay off based on due date

Reeza — Wed, 20 May 2020 17:36:28 GMT

Only other optimized way, I can think of, would be to do it for each day block so you're only reading each data set once. Is your date field indexed on your larger data set?
Actually doing it that way is probably as efficient as you can get since it's a single read of a data set to get the data each time and appends in.

Re: How to fetch total pay off based on due date

Trishjais — Fri, 29 May 2020 14:10:20 GMT

Thanks much for your suggestion, really appreciate