how to delete a substring in a character string

[jmckenzie]

I'm using SAS to format an excel file. Some of the rows in a particular field, Vendor, contain a VND number.  These entries show up as follows:

PIZZA HUT(VND+1234567890)

BILL SMITH(VND+9876543210)

Not all rows have a VND number.

For the rows that do, I would like to delete the (VND+XXXXXXXXXX)

I tried using Vendor=tranwrd(Vendor, '(VND+',' '); but can't seem to figure out how to add a wildcard after the +. 

Any suggestions what be greatly appreciated.  Perhaps tranwrd is not the best way to go about this?

[Steelers_In_DC]

This is close, I can't seem to figure out how to use VND+ as a string rather than 4 different delimiters, I'm hoping someone can shed some light on this:

data have;

infile cards dsd;

length name $50.;

format name $char50.;

input name;

cards;

PIZZA HUT(VND+1234567890)

BILL SMITH(VND+9876543210)

Extra Name(123456789)

;

run

data want(drop=name1 name2 name3);

set have(rename=(name=name1));

name2=scan(name1,1,'VND+');

name3=scan(name1,2,'VND+');

name=cats(name2,name3);

run;

;

[Peter_C]

"I can't seem to figure out how to use VND+ as a string rather than 4 different delimiters, "

Try FIND ()

something like 

partname = substr( name, 1, find( name!!"VND+", "VND+" )-1) ;

[Astounding]

If you do find "VND+", is there ever going to be any text after the closing parenthesis that you would want to keep?

[jmckenzie]

no, the (VND+XXXXXXXXXX) is always the at the end

[SASKiwi]

How about this if the VND is always on the end:

if index(vendor, '(VND') then vendor = substr(vendor, index(vendor, '(VND'));

[Astounding · Accepted solution]

A number of very similar solutions.  Here's my variation:

vnd_found = index(vendor, '(VND+');

if vnd_found > 1 then vendor = substr(vendor, 1, vnd_found-1);

I might be a little extra cautious here, but I imagined the possibility that vendor name was missing and that "(VND+XXXX)" began in column 1.  In that case, I would be keeping what is already there so you would have a chance to go back and find the vendor name based on the VND string.

Good luck.

[jmckenzie]

yes, that worked!  thank you so much!

[Ksharp]

This could be easy to do by SCAN().

x=scan(vendor,1,'()','m');