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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.