topic how to delete a substring in a character string in SAS Procedures

how to delete a substring in a character string

jmckenzie — Thu, 07 May 2015 17:59:21 GMT

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?

Re: how to delete a substring in a character string

Steelers_In_DC — Thu, 07 May 2015 18:24:11 GMT

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;

;

Re: how to delete a substring in a character string

Astounding — Thu, 07 May 2015 18:24:46 GMT

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

Re: how to delete a substring in a character string

jmckenzie — Thu, 07 May 2015 18:44:30 GMT

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

Re: how to delete a substring in a character string

Peter_C — Thu, 07 May 2015 19:45:51 GMT

"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) ;

Re: how to delete a substring in a character string

SASKiwi — Thu, 07 May 2015 19:50:06 GMT

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

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

Re: how to delete a substring in a character string

Astounding — Thu, 07 May 2015 20:21:50 GMT

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.

Re: how to delete a substring in a character string

jmckenzie — Thu, 07 May 2015 20:38:07 GMT

yes, that worked! thank you so much!

Re: how to delete a substring in a character string

Ksharp — Fri, 08 May 2015 15:03:05 GMT

This could be easy to do by SCAN().

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