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: 😳
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:
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.
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?
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.
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?
@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.
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! 😅
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!
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.
Ready to level-up your skills? Choose your own adventure.