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:
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.
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.