Macro variable with a wildcard within PROC SQL

[TS_Hum]

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;

[SASKiwi]

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;

[SASKiwi]

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;

[ballardw · Accepted solution]

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

[TS_Hum]

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!

[Tom]

---

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

[tarheel13]

What did the log say? 

[Sajid01]

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.