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;
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;
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;
You used underscores instead of % for your wild cards.
'20%\%on%'
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 %?
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.