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
