BookmarkSubscribeRSS Feed

vname to the rescue

Started ‎03-27-2019 by
Modified ‎07-18-2019 by
Views 1,707

I'm writing a user-written transformation for a whole series of DI jobs. I can't get all the metadata I need from the macro variables created for source table, so I did what I normally do: read dictionary.columns. And it worked fine - until one of the tables came from SQL Server with a non-standard name: TEST_TABLE$. This is represented as "TEST_TABLE$"n, and when I pass that into my query, it gets resolved as ""TEST_TABLE$"n", which won't work...

 

What to do? What to do? Hmm - how about this?

 

%let _target_name = "TEST_TABLE$"n;

data _null_;
retain &_target_name ' '; /* Prevent warning message */
call symput('meta_target_name', strip(vname(&_target_name))); /* vname strips the trailing 'n' and quotes */
run;

%put &=meta_target_name;

 

And it returns:

META_TARGET_NAME=TEST_TABLE$

 

I've only ever used vname and its friends for walking over an array of columns, and never for a single column. But in this case it did exactly what I wanted, and I was able to pass "&meta_target_name" directly into my query. So much easier than much more complicated ways of stripping of the trailing n and dequoting (or unquoting; I can never remember) the result.

 

I think I deserve a beer. As does the inventor of vname.

 

See also

Version history
Last update:
‎07-18-2019 08:19 AM
Updated by:

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


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 Labels
Article Tags