Using proc sql select - into with wildcards

[GeorgeBonanza]

I have a dataset that has one field where the values are

2017_12on15

2018_12on15

2019_12on12

2020_3on3

and then some other values that don't fit this pattern.  I want to select these values into macro variable but I can't seem to get it to work.

Here is what I have that isn't working.  Probably something simple that I am missing.  Any insight would be appreciated.

proc sql noprint; select FIELD1 into :MACRO_VAR separated by "|" from work.HAVE where FIELD1 like '20__\_%on%'; quit;

[PeterClemmensen · Accepted solution]

How about

data have;
input field1 $20.;
datalines;
2017_12on15
2018_12on15
2019_12on12
2020_3on3
;

proc sql noprint; 
   select FIELD1 into :MACRO_VAR separated by "|" 
   from work.HAVE where prxmatch('/\d{4}_\d+on\d+/', FIELD1); 
quit;

%put &MACRO_VAR;

[GeorgeBonanza]

This works. Thanks very much.

[Reeza]

You used underscores instead of % for your wild cards. 

'20%\%on%'

[GeorgeBonanza]

I appreciate you taking the time to respond. 

I thought "_" was the wildcard for a single character so if the pattern always starts "20##_", followed by either 1 or 2 digits, then the word "on", and finally another 1 or 2 digits, then wouldn't "__\_" capture the "##" and the underscore in "20##_"?  Then I would need to use % because the next piece can be 1 or 2 digits, similar to after "on" can be 1 or 2 digits.

Putting it all together I thought like '20__\_%on%' would work unless "\_%" is escaping both the underscore and %? 

[Reeza]

You're correct, I'm wrong on underscores not being wildcards.

https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473693.htm