BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Trishjais
Obsidian | Level 7

Hi Experts

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

 

cust_iddue_dt
14/2/2020
24/9/2020
34/17/2020
44/28/2020
184/30/2020
244/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_iddue_dtDel_amtOs_Amt
14/2/2020Del_amt from 20200402Del_amt tableOs_amt from 20200402Del_amt table
24/9/2020Del_amt from 20200409Del_amt tableOs_amt from 20200409Os_amt table
34/17/2020Del_amt from 20200417Del_amt tableOs_amt from 20200417Os_amt table
44/28/2020Del_amt from 20200428Del_amt tableOs_amt from 20200428Os_amt table
184/30/2020Del_amt from 20200430Del_amt tableOs_amt from 20200430Os_amt table
244/21/2020Del_amt from 20200421Del_amt tableOs_amt from 20200421Os_amt table
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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.

View solution in original post

4 REPLIES 4
Reeza
Super User
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.


Trishjais
Obsidian | Level 7
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
Reeza
Super User
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.
Trishjais
Obsidian | Level 7

Thanks much for your suggestion, really appreciate 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 506 views
  • 3 likes
  • 2 in conversation