BookmarkSubscribeRSS Feed
Sathya3
Obsidian | Level 7

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

 

 

 

7 REPLIES 7
Oligolas
Barite | Level 11

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._%"
________________________

- Cheers -

Sathya3
Obsidian | Level 7

That is not the issue.. 

Sathya3
Obsidian | Level 7
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
Barite | Level 11

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

- Cheers -

ballardw
Super User

@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
Jade | Level 19

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
Super User Tom
Super User

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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1471 views
  • 0 likes
  • 5 in conversation