BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Longimanus
Quartz | Level 8

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    ???!!!  🙏

 

1 ACCEPTED SOLUTION

Accepted Solutions
Longimanus
Quartz | Level 8

Aha ... so I need the full monty if I want to do it that way.  So you are saying that running with a Datastep would not help me a bit. I still cannot use the view. Right? 

View solution in original post

4 REPLIES 4
ballardw
Super User

If your source is actually a view then you can't update it. A View is basically a set of instructions on how to extract/manipulate values from existing data. As such attempting to "update values" doesn't mean anything as views do not actually contain values just those instructions.

 

 

Longimanus
Quartz | Level 8

Aha ... so I need the full monty if I want to do it that way.  So you are saying that running with a Datastep would not help me a bit. I still cannot use the view. Right? 

ballardw
Super User

@Longimanus wrote:

Aha ... so I need the full monty if I want to do it that way.  So you are saying that running with a Datastep would not help me a bit. I still cannot use the view. Right? 


Not to change values in a view.

 

Performance issues with large data sets I often suggest talking to the data base manager or other people involved in managing the data. It may be that you can provide them the data from SAS and have them update the data set. Or use "pass through" code to call external database management tools to work with that. I don't work with SPD so I'm not sure if this is an option.

Longimanus
Quartz | Level 8

It's almost midnight and nobody is on the system ... so I decided to run on the REAL DEAL. The big table in the SPD library. It ran through it all in less than 10 minutes. Yes ... and the updates are correct. What a relieve. Thanx for the feedback. Yes  ... I can retrieve a data from a view ... but not update it. "Of course" I think now. 😉 Duh! 😅

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 752 views
  • 0 likes
  • 2 in conversation