BookmarkSubscribeRSS Feed

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

Started ‎09-12-2022 by
Modified ‎09-12-2022 by
Views 775

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.


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)));


Version history
Last update:
‎09-12-2022 09:42 AM
Updated by:



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. 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags