SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

conditionally deleting part of an entry

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

conditionally deleting part of an entry

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!


Accepted Solutions
Solution
‎05-04-2015 04:28 AM
Respected Advisor
Posts: 3,908

Re: conditionally deleting part of an entry

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


All Replies
Respected Advisor
Posts: 3,908

Re: conditionally deleting part of an entry

data sample;

  input string $;

  length want $8;

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

  datalines;

1234510

8765420

457BH10

6g338k

;

run;

Super Contributor
Posts: 413

Re: conditionally deleting part of an entry

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?

Super User
Posts: 5,260

Re: conditionally deleting part of an entry

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

Data never sleeps
Solution
‎05-04-2015 04:28 AM
Respected Advisor
Posts: 3,908

Re: conditionally deleting part of an entry

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;

Trusted Advisor
Posts: 1,204

Re: conditionally deleting part of an entry

data want;

set have;

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

run;

Super Contributor
Posts: 413

Re: conditionally deleting part of an entry

hi sas@stat,

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

Super User
Posts: 6,982

Re: conditionally deleting part of an entry

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,204

Re: conditionally deleting part of an entry

Hi,

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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