topic deleting different sized parts of a cell in SAS Data Management

deleting different sized parts of a cell

ilikesas — Sun, 21 Dec 2014 14:34:48 GMT

Hi,

suppose I have a table with several columns:

name	info1	info2
ABC (gfd)	a	b
DEF ( h ffdds)	c	d
Z	e	f

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:

name	info1	info2
ABC	a	b
DEF	c	d
Z	e	f

Thank you!

Re: deleting different sized parts of a cell

art297 — Sun, 21 Dec 2014 14:53:47 GMT

data want;

set have;

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

run;

Re: deleting different sized parts of a cell

ilikesas — Sun, 21 Dec 2014 15:08:33 GMT

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

Re: deleting different sized parts of a cell

art297 — Sun, 21 Dec 2014 15:19:16 GMT

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

Re: deleting different sized parts of a cell

ilikesas — Sun, 21 Dec 2014 15:21:57 GMT

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

Re: deleting different sized parts of a cell

art297 — Sun, 21 Dec 2014 15:46:13 GMT

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;

Re: deleting different sized parts of a cell

ilikesas — Sun, 21 Dec 2014 15:52:36 GMT

Worked!!!

Thank a lot!!

Re: deleting different sized parts of a cell

Ksharp — Mon, 22 Dec 2014 08:24:51 GMT

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

Re: deleting different sized parts of a cell

ilikesas — Wed, 24 Dec 2014 04:58:58 GMT

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

Re: deleting different sized parts of a cell

RamKumar — Wed, 24 Dec 2014 06:41:54 GMT

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.

Re: deleting different sized parts of a cell

Ksharp — Wed, 24 Dec 2014 06:58:49 GMT

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