Help using Base SAS procedures

how to delete a substring in a character string

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

how to delete a substring in a character string

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?


Accepted Solutions
Solution
‎05-07-2015 04:21 PM
Super User
Posts: 5,505

Re: how to delete a substring in a character string

Posted in reply to jmckenzie

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.

View solution in original post


All Replies
Valued Guide
Posts: 860

Re: how to delete a substring in a character string

Posted in reply to jmckenzie

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;

;

Valued Guide
Posts: 2,177

Re: how to delete a substring in a character string

Posted in reply to Steelers_In_DC

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


Super User
Posts: 5,505

Re: how to delete a substring in a character string

Posted in reply to jmckenzie

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

Occasional Contributor
Posts: 10

Re: how to delete a substring in a character string

Posted in reply to Astounding

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

Super User
Posts: 3,254

Re: how to delete a substring in a character string

Posted in reply to jmckenzie

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

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

Solution
‎05-07-2015 04:21 PM
Super User
Posts: 5,505

Re: how to delete a substring in a character string

Posted in reply to jmckenzie

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.

Occasional Contributor
Posts: 10

Re: how to delete a substring in a character string

Posted in reply to Astounding

yes, that worked!  thank you so much!

Super User
Posts: 10,028

Re: how to delete a substring in a character string

Posted in reply to Astounding

This could be easy to do by SCAN().

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 320 views
  • 4 likes
  • 6 in conversation