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

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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