topic Re: Using proc sql select - into with wildcards in SAS Programming

Using proc sql select - into with wildcards

GeorgeBonanza — Thu, 09 Apr 2020 19:35:27 GMT

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;

Re: Using proc sql select - into with wildcards

PeterClemmensen — Thu, 09 Apr 2020 19:42:06 GMT

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;

Re: Using proc sql select - into with wildcards

GeorgeBonanza — Thu, 09 Apr 2020 19:51:30 GMT

This works. Thanks very much.

Re: Using proc sql select - into with wildcards

Reeza — Thu, 09 Apr 2020 21:18:07 GMT

You used underscores instead of % for your wild cards.

'20%\%on%'

Re: Using proc sql select - into with wildcards

GeorgeBonanza — Fri, 10 Apr 2020 16:05:02 GMT

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 %?

Re: Using proc sql select - into with wildcards

Reeza — Mon, 13 Apr 2020 18:37:05 GMT

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