Splitting/Extracting numeric values from a character variable

[Gladis6680]

Hi there,

I have asked this question in another thread, but I am not sure if it belongs there.

I have a character variable with lots numeric values as well. I wanted to extract the numeric values and put them into another field.

For example:  What i have is the variable on the left and I would like to split the HAVE variable so that I could get the quantity which is on the right.

I would really appreciate some help

HAVE	Quantity
Sauce Alfredo #2 4/2 Kg	4/2 Kg
Sauce Alfredo 2/3.7 Lt	2/3.7 Lt
Assist 1Al1 1/18.9 LT	1/18.9 LT
Assouplissant Solid Navisoft Bp S#Ncf# 2/6Lb	2/6Lb
100% Whole Wheat 1/675 Gr	1/675 Gr
15X20X1/2 White Cut/Board 6/1 Ea	6/1 Ea
16Oz White Plastic Funnel 1/1 Ea	1/1 Ea
Spoon Soup Celine 18/0 Round 7 - 17.8Cm (502513) 12/1 Ea	12/1 Ea
Spoon Soup Ecopro Poly 1/1000 Ea (10057937706427)	1/1000 Ea
16Oz White Plastic Funnel 1/1 Ea	1/1 Ea
A&W Diet Root Beer 12/355 Ml	12/355 Ml
A&W Rootbeer Fridge Pack 12/355 Ml	12/355 Ml
Bag Garbage 35X50 Regular Black 1/100 Ct	1/100 Ct
Bar Granola Fruit & Nut Trail Mix (000065633408811) 8/12/35 Gr	8/12/35 Gr

[ballardw]

Missing details needed:

Does every single one of your desired values contain a / character?

Is that the only place that a / character appears in the data?

Is the value prior to the first / always immediately adjacent to the / character (no spaces for instance) and is it always numeric?

If this were my project I might ask around to see if there is an Inventory management program already in place. Many times those will already have fields related to packaging and contents per package. It might be easier to extract the information you need from that program, especially if the answer to any of my questions is "no".

[Gladis6680]

Missing details needed:

Does every single one of your desired values contain a / character?

Answer: Yes

Is that the only place that a / character appears in the data?

Answer: I am not sure if I understand, the measurement usually appears at the end and the description (character) appears at the end

Is the value prior to the first / always immediately adjacent to the / character (no spaces for instance) and is it always numeric?

Answer:Yes

If this were my project I might ask around to see if there is an Inventory management program already in place. Many times those will already have fields related to packaging and contents per package. It might be easier to extract the information you need from that program, especially if the answer to any of my questions is "no".

Answer:I have tried that and I haven't heard back

[ballardw]

---

@Gladis6680 wrote:

Missing details needed:

Is that the only place that a / character appears in the data?

Answer: I am not sure if I understand, the measurement usually appears at the end and the description (character) appears at the end

---

You show an example:

"Spoon Soup Celine 18/0 Round 7 - 17.8Cm (502513) 12/1 Ea" where you want the "12/1 Ea" as result.

The first / occurs before the values you want.

The point is we need to know what we are going to search for to determine the value.

Your example does not have a consistent starting place as far as I can tell, or a consistent number of words at the end of the description.

"2/6Lb" would be one word, most of the examples such as "1/1000 Ea" could be two words. If the desired bit was always exactly two words there would be a consistent pattern to extract, or if the first / was always in the count you want we could identify the position of /, go back to the beginning of what preceded the / and be good. But the data shows that those can't be the rules.

"Spoon Soup Ecopro Poly 1/1000 Ea (10057937706427)" also requires extracting and then removing bits after guessing where a measure might end.

We could likely come up with something that will get a high percentage of the values that you could manually modify.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against. It is important to paste the code into a code box as the forum will reformat text in he main message window and often renders code not runnable.

[Gladis6680]

You show an example:

"Spoon Soup Celine 18/0 Round 7 - 17.8Cm (502513) 12/1 Ea" where you want the "12/1 Ea" as result.

The first / occurs before the values you want.

Answer: Yes you are right, I apologize, the "/"  can occur anytime, I just want the quantity.  

Your example does not have a consistent starting place as far as I can tell, or a consistent number of words at the end of the description.

Answer: Yes you are right, it can be one word and it can be 2 words or even 3 words.

I do understand that there is no set pattern, which makes it extremely difficult and I also understand I may not be able to get the desired result for all the rows. 

But anything to just get me started would be great.

Also I had a slightly different request in another post,(How to split a character variable), where I only needed the description and not the quantity and one of the super users were able to get me started using the command below. This may be a silly question, but does the deleted characters get stored somewhere and if so, are we able to pull that info

prxchange('s/\S+\s*(Ea|Ct|Kg|Gr|Lt|Lb|Ml)\b//i',-1,have);

Thanks a lot

[ballardw]

---

@Gladis6680 wrote:

Also I had a slightly different request in another post,(How to split a character variable), where I only needed the description and not the quantity and one of the super users were able to get me started using the command below. This may be a silly question, but does the deleted characters get stored somewhere and if so, are we able to pull that info

prxchange('s/\S+\s*(Ea|Ct|Kg|Gr|Lt|Lb|Ml)\b//i',-1,have);

Thanks a lot

---

I'm not a whiz with the regular expressions but you can use Call PRXPOSN - before prxchange to extract a matched string.