Tip: How to parse table/column names with special characters

[#1]

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:

accountType

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.

[Tom]

You seem to have buried the lede in this article.

Yes the DEQUOTE() function has a "feature" that removes everything after the closing quote, making it very useful for converting a name literal into a name.

There is no need to call the datastep SCAN() function as macro code already has its own %SCAN() function.  Plus you should use the Q modifier on the %SCAN() function call to protect against table names with periods in them.

Also you should use %QSYSFUNC() to add macro quoting to any characters in the name that might confuse your macro code. 

%let _source = srcref."accountType"n;
%let table = %qsysfunc(dequote(%scan(&_source,2,.,q)));