Often while doing ETL tasks I have to overcome an issue that arises when the same variable has char format in table A and varchar in table B, both come from caslibs.
This could affect many variables and I don't want to write extra code to create new variables with varchar in table A, drop the old ones and rename the new ones accordingly.
I use a "trick" which works but there must be a better solution: I write both CAS tables to the work library and back to CAS. This has the effect that all character variables have char format as they get converted to char because work directory cannot maintain varchar format.
What Do you recommend?
I haven't a lot of actual project experience yet with ETL under Viya but I've spent already quite a bit of experimenting and thinking on it.
Where I've ended up so far: I'd keep most of ETL processing under compute where you've got only CHAR and then load the final "analytics" tables to CAS. With Viya 4 I'm thinking that the "end-point" of ETL under compute could be permanent parquet tables that then get loaded server side to CAS.
In my thinking the only data prep processing in CAS would be things like match code generation and the like where performance due to its resource intensity would likely profit a lot from multithreaded in-memory processing.
Now... with "my" approach there shouldn't be much of a varchar issue. If you want more data management in CAS then I'd ask how you end up with such "mixed" data types in first place? If you have control over the load into CAS then that's where you should ensure that the "same" variable always gets the same attributes in all CAS tables. At least with recent Viya versions also the loadTable action for .csv sources allows to define per variable what data type it should map to.
If you really have to change the data type in a CAS table: Why not use the same approach that you would use for a "normal" SAS table? Create a new variable with an attrib/length statement that's of type char, assign the varchar variable to it, drop the varchar variable at the end. And also the same approach to make such a process dynamic via SAS macro language/SQL query of dictionary tables to generate such attrib, assignment and drop/rename statements.
Where I am right now: Not a too big fan of the SAS Viya implementation of Varchar given all the documented restrictions Restrictions for the VARCHAR Data Type and given the fact that any Varchar in CAS requires at least 16 bytes of storage.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.