Hello,
Lately I had to switch from SAS 9.4 to SAS Viya 4 and I'm not familiar with this environment. I have to create ETL processes in my project. I want to load a table from the source data base to CAS in sashdat format, and then join them together in final in-memory table. On average I have 10 source tables per report. Tables have a few milion records each and need to be refreshed daily. If You could send me any ideas how to make this process optimized I'd be grateful.
How did you handle this in SAS9? Were you using code or point and click?
Recent Viya 4 versions got SAS Studio Engineer (needs to be licensed) that adds transformations similar to SAS DI Studio under 9.4.
Viya is a big architectural change as compared to SAS9.4 and we need to start doing some things differently. Make sure you get proper training before you start with migration or new implementation under Viya.
Have you explored the CASUTIL procedure? This is probably a good place to start since you haven't told us what you have tried so far.
Generally speaking, getting the data into CAS represents the L in ETL.
CAS was designed for analytic processing primarily, and isn't necessary good for (all) ETL use cases.
When you say "...and then join them together in final in-memory table". Are you actually joining them, or use a start schema/view in CAS?
A few million records doesn't sound like a lot, so it should not be a big performance issue for a daily refresh.
Perhaps if you could be more specific in what your process look like and what you you need help with?
Have tried something so far?
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.