My user-written SAS Data Integration Studio code has to be able handle anything that gets thrown at it. And because I have no control over how the source or target tables have been defined, that includes source tables that have "Enable special characters within table or column object names" turned on. There's never any reason for this (as most source systems won't have column names with spaces or special characters), and it's probably because the developer who created the metadata for the table had their options set up poorly. Nevertheless the metadata has been constructed like that, and I have to cope with it.
So I end up with a table name in the job defined in DI as:
%let _source = srcref."accountType"n;
When I compare that table against the target's target.acccounttype, even accounting for case, it fails because it's not expecting the quotes and n suffix.
I thought I'd patched the code to account for all of these, but another one cropped up this morning with a new macro where I'd forgotten to include this little beauty:
%let table = %sysfunc(dequote(%sysfunc(scan(&&&table, 2, %str(.)))));
where table contains _source.
The dequote function simply strips quotes off strings, but what I infer it's doing is taking off the first quote, walking along the string until it finds the last quote and stopping. So the result in table is simply:
This I can use, and the code no longer throws errors.
Previously I'd created a DATA step where I used vname function to do the same thing (another handy side-effect), but that isn't available in %sysfunc.
From the time the process specialist told me we had lots of jobs which had failed, until I'd changed the code, tested and pushed it for testing: 15 minutes.
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.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.