Pattern matching issue

[Sathya3]

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

[Oligolas]

Hi,

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

 memname like "T_cdr_&yyyymm_%"

To

 memname like "T_CDR_&yyyymm._%"

[Sathya3]

That is not the issue.. 

[Sathya3]

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

[Oligolas]

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))

[ballardw]

---

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

[FreelanceReinh]

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))

[Tom]

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;