topic Re: Macro variable with a wildcard within PROC SQL in SAS Programming

Macro variable with a wildcard within PROC SQL

TS_Hum — Wed, 23 Jun 2021 20:12:04 GMT

I am trying to identify a list of table containing certain values in their names (relying on an internal naming convention). My attempt is failing and I believe it's likely a simple syntax error, but I am just not seeing it. I am trying to use variables (second block of code below) to replicate the results received when using direct entry (first block). Please help!

/******************This version works*******************/
PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
where memtype = 'DATA'
and memname like '%0621%'
and memname like '%TJR%';
QUIT;

/******************This version does not*******************/
%let RUN = 0621;
%LET BUNDLE = TJR;

PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
where memtype = 'DATA'
and memname like "%&RUN%"
and memname like "%&BUNDLE%";
QUIT;

Re: Macro variable with a wildcard within PROC SQL

SASKiwi — Wed, 23 Jun 2021 20:20:18 GMT

Try this:

%let RUN = 0621;
%LET BUNDLE = TJR;

PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
where memtype = 'DATA'
and memname like "%&RUN.%"
and memname like "%&BUNDLE.%";
QUIT;

Re: Macro variable with a wildcard within PROC SQL

ballardw — Wed, 23 Jun 2021 20:20:50 GMT

The warning you should have gotten in the log

58   and memname like "%&BUNDLE%";
WARNING: Apparent invocation of macro TJR not resolved.
NOTE: No rows were selected.
59   QUIT;

gives you clue about it involves macro resolution and %anytext would reference a macro named Anytext.

Try

%let RUN = 0621;
%LET BUNDLE = TJR;

PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
where memtype = 'DATA'
and memname like "%&RUN%"
and memname like "%%&BUNDLE%";
QUIT;

The double %% is sort of an escape to avoid the resolved value of Bundle treated as a macro call.

You may need to do the same with RUN. It didn't have an issue with your explicit example because 0621 is not a valid macro name. If the value of Run was something that starts with a letter or _ underscore it will generate the same "not resolved message".

Re: Macro variable with a wildcard within PROC SQL

tarheel13 — Wed, 23 Jun 2021 20:21:27 GMT

What did the log say?

Re: Macro variable with a wildcard within PROC SQL

SASKiwi — Wed, 23 Jun 2021 20:23:51 GMT

Actually this is probably better:

%let RUN = 0621;
%LET BUNDLE = TJR;

PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
where memtype = 'DATA'
and memname like "%str(%)&RUN%str(%)"
and memname like "%str(%)&BUNDLE%str(%)";
QUIT;

Re: Macro variable with a wildcard within PROC SQL

Sajid01 — Wed, 23 Jun 2021 20:29:24 GMT

1.Suggest using an OR instead of AND before the second memname as shown below

PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
where memtype = 'DATA'
and memname like "%&RUN.%"
OR  memname like "%TJR%";
QUIT;

You may not be having any dataset with with string TJR in it.
I replaced TJR with CL and it worked.

Re: Macro variable with a wildcard within PROC SQL

TS_Hum — Thu, 24 Jun 2021 13:08:57 GMT

That was it! Thanks so much for taking a look. It hadn't occurred to me that the wildcard in SQL happens to also match the syntax for calling a macro. My background is in SQL and VBA/VB, but I've only been into SAS for a month or so. It's taking a little while to get used to focusing as much on the log as the results window. There's nothing quite like staring at a piece of code for hours/days only to find I've missed a single character. This is the first time I actually got on a forum and asked for help. Thanks again!

Re: Macro variable with a wildcard within PROC SQL

Tom — Thu, 24 Jun 2021 14:16:49 GMT

@TS_Hum wrote:
That was it! Thanks so much for taking a look. It hadn't occurred to me that the wildcard in SQL happens to also match the syntax for calling a macro. My background is in SQL and VBA/VB, but I've only been into SAS for a month or so. It's taking a little while to get used to focusing as much on the log as the results window. There's nothing quite like staring at a piece of code for hours/days only to find I've missed a single character. This is the first time I actually got on a forum and asked for help. Thanks again!

The SAS log is your friend. As much as we all complain about issues we might have with the SAS log it is still the best I have seen for any programming system I have used.