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

deleting different sized parts of a cell

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

deleting different sized parts of a cell

Hi,

suppose I have a table with several columns:

nameinfo1info2
ABC (gfd)ab
DEF ( h  ffdds)cd
Zef

What I would like to do if possible is to delete the parentheses for the name column entries and all that is inside those parentheses, and note that the length of the part that I want to be deleted varies from entry to entry, and I have many rows... And the entries without the parentheses keep as is

So the end result should be like this:

nameinfo1info2
ABCab
DEFcd
Zef

Thank you!


Accepted Solutions
Solution
‎12-21-2014 10:46 AM
PROC Star
Posts: 7,487

Re: deleting different sized parts of a cell

That is what I would do:

data have;

  informat name $25.;

  input name &;

  cards;

ABC (fjfj fjfj l ) DEF

;

data want;

  set have;

  name=catt(scan(name,1,'('),scan(name,-1,')'));

run;

View solution in original post


All Replies
PROC Star
Posts: 7,487

Re: deleting different sized parts of a cell

data want;

  set have;

  name=scan(name,1,'(');

run;

Super Contributor
Posts: 441

Re: deleting different sized parts of a cell

Thanks Dr for the quick reply, it worked perfectly!!!

I guess that the meaning of the code is that it deletes for a given entry everything after and including the first ( and returns the entry in its new form

PROC Star
Posts: 7,487

Re: deleting different sized parts of a cell

Mostly, but I would say that it extracts all of the characters before the first '('.

Super Contributor
Posts: 441

Re: deleting different sized parts of a cell

And what if the parentheses are in the middle of the entry, like:

ABC (fjfj fjfj l ) DEF

and the result that we want to get if ABC DEF ?

Does the code for this require 2 scans and a concatenation?

Thank you

Solution
‎12-21-2014 10:46 AM
PROC Star
Posts: 7,487

Re: deleting different sized parts of a cell

That is what I would do:

data have;

  informat name $25.;

  input name &;

  cards;

ABC (fjfj fjfj l ) DEF

;

data want;

  set have;

  name=catt(scan(name,1,'('),scan(name,-1,')'));

run;

Super Contributor
Posts: 441

Re: deleting different sized parts of a cell

Worked!!!

Thank a lot!!

Super User
Posts: 10,041

Re: deleting different sized parts of a cell

Perl Regular Expression Version :


data have;
  informat name $25.;
  input name &;
  cards;
ABC (gfd)
ABC (fjfj fjfj l ) DEF
DEF ( h ffdds)
;
 
data want;
  set have;
  name=prxchange('s/\(.*\)//o',-1,name);
run;

Xia Keshan

Super Contributor
Posts: 441

Re: deleting different sized parts of a cell

Thanks xia keshan, I wish I could put more than one correct answer!!

Regular Contributor
Posts: 168

Re: deleting different sized parts of a cell

What if we've '%' around characters? e.g. ABC %fjfj fjfj l% DEF

I need the output as ABC DEF. I'm curious to know the perl regular expression version for this case.

Super User
Posts: 10,041

Re: deleting different sized parts of a cell

OK. Here is . Peal Regular Expression   \(.*\)|%.*%  just match the pattern  (fjfj fjfj l )  or    %fjfj fjfj l%  , you cold check it more in the sas documentation.


data have;
  informat name $25.;
  input name &;
  cards;
ABC (gfd)
ABC (fjfj fjfj l ) DEF
DEF ( h ffdds)
ABC %fjfj fjfj l% DEF
;
 
data want;
  set have;
  name=prxchange('s/\(.*\)|%.*%//o',-1,name);
run;

Xia Keshan

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 482 views
  • 6 likes
  • 4 in conversation