Hi folks! I need help!
I have a problem not even ChatGTP can solve. 🙄 Sure the suggested solutions look pretty good but there is always something wrong when I try to run it. I tried to change and "tell" ChatGTP something is wrong, and then it starts: You are completely right. I will send you the correct code. Well ... that also doesn't work. At some point the BOT gets it ... but not yet.
My problem: 😳
- I need to update one variable altered_dttm in table TABLE_1 which is SO HUGE it's residing in our SPD-library, SPD_LIB.
- There is a view on this table that only points to 1/3 of it, so I already planned to avoid to use the entire table in the final solution. Let's for the sake of the example say the view is TABLE_1 in LIB_X. I first coded a test SQL program to see if the code works on 1/1000.000th of all the rows in the actual table.
- Then there is a lookup table TABLE_2 in WORK with the same variable altered_dttm. Yes they have the same name and this should not be a problem. ChatGTP likes me to rename the one in the lookup table.
- Both tables have a unique composite key (PRIMKEY) build by the variables account_key and valid_from_dt.
You guessed it ... I need to replace the variable altered_dttm in table TABLE_1 if it is missing but not missing in TABLE_2. That is all really. Should be simple. Apparently not when it's HUGE for SQL ... or in a data step (replace) with a composite key.
So to test my SQL program I took a a tiny part of the TABLE_1 and called it TABLE_1_TEST in WORK. I made sure that all the relevant observations (with the same account_key and valid_to_dttm. ) were present and some more, to test-run and see if it worked.
My test program:
%LET T1 = TABLE_1_TEST;
%LET T2 = TABLE_2;
%LET Variable = ALTERED_DTTM;
proc sql;
update &T1. as a
set &Variable. = (select b.&Variable.
from &T2 as b
where a.account_key = b.account_key
and a.valid_from_dt = b.valid_from_dt)
where missing(a.&Variable.)
and exists (select 1
from &T2. as b
where a.account_key = b.account_key
and a.valid_from_dt = b.valid_from_dt)
;quit;
That SQL works exactly as I want. 🙂 I got all the missing dates written in the observations where they were missing. A happy man! 😊
Then I tried to run it on the view.
%LET T1 = LIB_X.TABLE_1; /* So I chose a view and not the entire MEGA table that resides in SPD_LIB ! */
%LET T2 = TABLE_2;
%LET Variable = ALTERED_DTTM;
The code started to run but ... nothing seemed to happen. Or maybe it was. I checked in putty (not sure how you guys monitor your PIDS) and the process was using almost a 100% of the CPU but the status was S ... sleeping. Waiting for something. Nobody on the system. I am not sure how SQL works with this when the amounts of data become 10.000 fold. I canceled by job after two hours and checked. Nothing was changed. Actually good for the moment. You don't want half of it done ...
I mentioned this to ChatGTP. I got an explanation that SQL can have difficulties with huge indexed tables. It suggested to fix it with a a modify-replace datastep or with hash object approach. I preferred the former since it looks familiar. It never came with a solution that worked. The problem being the composite key. It came with various "key=" options but non that worked. After various tries I thought: let's try that hash option. Also didn't work.
I feel a little embarrassed to ask you guys since this should be school stuff. But I never solved this with a table this size. I am sure a Data Step with modify 'n replace can use the index properly and not run through the entire table (if that is what SQL is doing ... worked fine on my little test dataset but not on REAL deal.). S o a n y b o d y ???!!! 🙏
