BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jmckenzie
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Steelers_In_DC
Barite | Level 11

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
Rhodochrosite | Level 12

"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
PROC Star

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

jmckenzie
Calcite | Level 5

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

SASKiwi
PROC Star

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

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

Astounding
PROC Star

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
Calcite | Level 5

yes, that worked!  thank you so much!

Ksharp
Super User

This could be easy to do by SCAN().

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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