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!
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;
data sample;
input string $;
length want $8;
want=prxchange('s/([12]0)[[:blank:]]*$//o',1,string);
datalines;
1234510
8765420
457BH10
6g338k
;
run;
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?
You could embed it in a user defined function (PROC FCMP), and thus make it easy to send parameters.
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;
data want;
set have;
if substr(entry,length(entry)-1) in ('10','20') then entry=substr(entry,1,length(entry)-2);
run;
hi sas@stat,
I ran your code but unfortunately the new "entry" column was all empty ...
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 |
Obs | entry |
1 | 12345 |
2 | 87654 |
3 | 457BH |
4 | 6g338k |
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.
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.