BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

Hi,

suppose I have the following entries:

entry
1234510
8765420
457BH10
6g338k

What I would like to get is the following: if the last 2 characters of the entry are either 10 or 20, then delete these two characters so that the final result is like this:

entry
12345
87654
457BH
6g338k

thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

I guess we would need to generate the Regular Expression but if it's as simple as just a list of a few digits then this shouldn't be too hard.

Some code like below would do:

data list;

  length str $3;

  str='10';output;

  str='20';output;

  str='38k';output;

  stop;

run;

proc sql noprint;

  select strip(str) into :rem_list separated by '|'

  from list

  ;

quit;

%put rem_list: &rem_list;

data sample;

  input string $;

  length want $8;

  want=prxchange("s/(&rem_list)[[:blank:]]*$//o",1,string);

  datalines;

1234510

8765420

457BH10

457BH30

6g338k

;

run;

View solution in original post

8 REPLIES 8
Patrick
Opal | Level 21

data sample;

  input string $;

  length want $8;

  want=prxchange('s/([12]0)[[:blank:]]*$//o',1,string);

  datalines;

1234510

8765420

457BH10

6g338k

;

run;

ilikesas
Barite | Level 11

Hi Patrick,

ran your code and got the result that I wanted.

I was just wandering, is it possible to generalize your code so that it can delete any given last numbers?

LinusH
Tourmaline | Level 20

You could embed it in a user defined function (PROC FCMP), and thus make it easy to send parameters.

Data never sleeps
Patrick
Opal | Level 21

I guess we would need to generate the Regular Expression but if it's as simple as just a list of a few digits then this shouldn't be too hard.

Some code like below would do:

data list;

  length str $3;

  str='10';output;

  str='20';output;

  str='38k';output;

  stop;

run;

proc sql noprint;

  select strip(str) into :rem_list separated by '|'

  from list

  ;

quit;

%put rem_list: &rem_list;

data sample;

  input string $;

  length want $8;

  want=prxchange("s/(&rem_list)[[:blank:]]*$//o",1,string);

  datalines;

1234510

8765420

457BH10

457BH30

6g338k

;

run;

stat_sas
Ammonite | Level 13

data want;

set have;

if substr(entry,length(entry)-1) in ('10','20') then entry=substr(entry,1,length(entry)-2);

run;

ilikesas
Barite | Level 11

hi sas@stat,

I ran your code but unfortunately the new "entry" column was all empty ...

Kurt_Bremser
Super User

You must have made an error, look at the log.

This code:

data have;

infile cards;

input entry: $7.;

cards;

1234510

8765420

457BH10

6g338k

;

run;

data want;

set have;

if substr(entry,length(entry)-1) in ('10','20') then entry=substr(entry,1,length(entry)-2);

run;

proc print data=have;

proc print data=want;

run;

gives this result:

Obs entry

1 1234510
2 8765420
3 457BH10
4 6g338k
        

Obsentry

1 12345
2 87654
3 457BH
4 6g338k
stat_sas
Ammonite | Level 13

Hi,

Please check it again as mentioned above. This works for me.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1867 views
  • 6 likes
  • 5 in conversation