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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1210 views
  • 6 likes
  • 5 in conversation