topic Re: Pattern matching issue in SAS Programming

Pattern matching issue

Sathya3 — Wed, 02 Aug 2023 13:34:27 GMT

%let YYMMM=202303;

Proc sql;

create table table_list as

select memname from dictionary.tables

where libname='HVGRP'

and memname like "T_cdr_&yyyymm_%"

order by input(substr(memname,length("T_CDR_&YYYYMM._")+1),8.);

quit;

I want memname to adhere to below specifications:

To correctly match values like "T_CDR_202303_1", "T_CDR_202303_2" and so on "T_CDR_202303_n", ( where n is not a fixed integer and it starts from 1 )

It should not match patterns other than above .It should exclude patterns for eg: "T_CDR_202303_SVB_0712" ,

"T_CDR_202303_1_BKP"

Re: Pattern matching issue

Oligolas — Wed, 02 Aug 2023 13:47:11 GMT

Hi,

dictionnary table entries are upper case so you probably only need to modify

 memname like "T_cdr_&yyyymm_%"

 memname like "T_CDR_&yyyymm._%"

Re: Pattern matching issue

Sathya3 — Wed, 02 Aug 2023 14:01:20 GMT

That is not the issue..

Re: Pattern matching issue

Sathya3 — Wed, 02 Aug 2023 14:05:47 GMT

data t_cdr_202212_2;
input name $;
datalines;
aa
bb
;
run;

data t_cdr_202212_10;
input name $;
datalines;
aa
bb
;
run;

data t_cdr_202212_10_BKP;
input name $;
datalines;
aa
bb
;
run;

data t_cdr_202212_HVB_10_BKP;
input name $;
datalines;
aa
bb
;
run;


%macro test;
  /* Create table_list using DICTIONARY.TABLES */
%let YYMMM = 202212;


   PROC SQL;
    CREATE TABLE table_list AS 
    SELECT memname 
    FROM dictionary.tables
    WHERE libname = 'WORK'
      AND memname like "T_CDR_&YYMMM._%"
     ;

QUIT;
  
%mend test;
%test;

I am providing sample code..

Re: Pattern matching issue

ballardw — Wed, 02 Aug 2023 14:50:34 GMT

@Sathya3 wrote:

It should not match patterns other than above .It should exclude patterns for eg: "T_CDR_202303_SVB_0712" ,

"T_CDR_202303_1_BKP"

What makes you think that? There is nothing in the code

 memname like "T_CDR_&yyyymm_%"

which matches anything starting with those. Or are you requesting such?

and input(scan(memname,4,'_'),f4.)>0

perhaps.

Or look at the PRX functions that you can specify that digits appear in specific places in the pattern.

Re: Pattern matching issue

FreelanceReinh — Wed, 02 Aug 2023 16:35:16 GMT

Hello @Sathya3,

You would need to use an escape character to match a literal underscore with the LIKE operator. But the condition "only digits after the underscore following the date string" exceeds the capabilities of the LIKE operator. Therefore, I suggest a condition using a Perl regular expression instead of the LIKE condition:

prxmatch("/^T_CDR_&YYMMM._\d+$/",trim(memname))

where "\d+$" matches one or more digits at the end of trim(memname).

Edit: ... and the "^" sign matches the beginning of the string.

Edit 2:

@Sathya3 wrote:

To correctly match values like "T_CDR_202303_1", "T_CDR_202303_2" and so on "T_CDR_202303_n", ( where n is not a fixed integer and it starts from 1 )

It should not match patterns other than above .

If names with leading zeros in the number at the end must be excluded, use this modification of the regex:

prxmatch("/^T_CDR_&YYMMM._[1-9]\d*$/",trim(memname))

Re: Pattern matching issue

Tom — Wed, 02 Aug 2023 19:16:14 GMT

So you want to exclude those that do not have a numeric suffix?

One way to try check if a string of characters is number is to try to convert it to a number using the INPUT() function. Perhaps you could just simplify to checking the characters after the last underscore.

%let YYMMM=202303;
proc sql;
create table table_list as 
  select memname
       , input(scan(memname,-1,'_'),?32.) as suffix
  from dictionary.tables
  where libname='HVGRP'
    and memname like "T^_CDR^_&yyyymm.^_%" escape '^'
    and not missing(calculated suffix)
  order by suffix
;
quit;

Re: Pattern matching issue

Oligolas — Thu, 03 Aug 2023 05:44:06 GMT

I see, you want also to exclude the other terms.

Then a regular expression should do the job:

where libname='HVGRP'
and prxmatch("/^T_CDR_&YYMMM._\d+$/",strip(memname))