topic Re: How to split a character variable in SAS Programming

How to split a character variable

Gladis6680 — Tue, 11 Jun 2019 19:57:11 GMT

Hi there,

I have a character variable called Description

HAVE	WANT
Sauce Alfredo #2 4/2 Kg	Sauce Alfredo
Sauce Alfredo 2/3.7 Lt	Sauce Alfredo
Assist 1Al1 1/18.9 LT	Assist 1Al1
Assouplissant Solid Navisoft Bp S#Ncf# 2/6Lb	Assouplissant Solid Navisoft Bp S#Ncf#
100% Whole Wheat 1/675 Gr	100% Whole Wheat
15X20X1/2 White Cut/Board 6/1 Ea	15X20X1/2 White Cut/Board
16Oz White Plastic Funnel 1/1 Ea	16Oz White Plastic Funnel
A&W Diet Root Beer 12/355 Ml	A&W Diet Root Beer
A&W Rootbeer Fridge Pack 12/355 Ml	A&W Rootbeer Fridge Pack
Almond Slivered Blanched (20067261010715) 2/1.50 Kg	Almond Slivered Blanched (20067261010715)
Almond Slivered Blanched 1/3 Kg	Almond Slivered Blanched
Almond Whole Blanched (20067261010418) 2/1.50 Kg	Almond Whole Blanched (20067261010418)
Bacon 14/16 Ct Center Cut 8.75 In Sliced 1/5 Kg	Bacon 14/16 Ct Center Cut 8.75 In Sliced
Bacon 16/18 Ct Applewood Smoked 1/5 Kg	Bacon 16/18 Ct Applewood Smoked
Bacon 16/18 Ct Center Cut 1/5 Kg	Bacon 16/18 Ct Center Cut
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct	Bag Garbage 35X50 Heavy Duty Natural
Bag Garbage 35X50 Regular Black 1/100 Ct	Bag Garbage 35X50 Regular Black
Bar Granola Fruit & Nut Trail Mix (000065633408811) 8/12/35 Gr	Bar Granola Fruit & Nut Trail Mix (000065633408811)
Bar Granola Oats N' Honey Crunchy 6/12/46 Gr	Bar Granola Oats N' Honey Crunchy
Spoon Set Measure SS 6/1 Ea	Spoon Set Measure SS
Spoon Soup Celine 18/0 Round 7 - 17.8Cm (502513) 12/1 Ea	Spoon Soup Celine 18/0 Round 7 - 17.8Cm (502513)
Spoon Soup Ecopro Poly 1/1000 Ea (10057937706427)	Spoon Soup Ecopro Poly (10057937706427)
Spoon Soup Medium Weight Polypro Dense (00620868514701) 1/1000 Ct	Spoon Soup Medium Weight Polypro Dense (00620868514701)
Spoon Wooden 18 In W/Long Handle 1/1 Ea	Spoon Wooden 18 In W/Long Handle
Spoon Wooden Heavy Duty 16 In - 40.6 Cm 1/1 Ea (575386)	Spoon Wooden Heavy Duty 16 In - 40.6 Cm (575386)
Spread Apple Raspberry 1/14 Kg	Spread Apple Raspberry

I would like to take the Quantity out for eg (Ea, Ct, Kg, Kg), some of the Ea's are in between the description.

I would appreciate any help.

Thanks

Re: How to split a character variable

Reeza — Tue, 11 Jun 2019 20:03:47 GMT

Data cleaning. Welcome to the world of fun with functions.

Functions that will be helpful here:

COMPRESS()
TRANSLATE
TRANWRD
SCAN()
COUNTW()
FIND/FINDW
INDEX/INDEXW
and
Regular Expressions.

TRANWRD will replace words, translate will replace strings.
SCAN() will separate words
COUNTW() will count the number of words

Re: How to split a character variable

Gladis6680 — Tue, 11 Jun 2019 20:13:42 GMT

Is it possible to give me an example with some code

Thanks

Re: How to split a character variable

ballardw — Tue, 11 Jun 2019 20:55:34 GMT

If this were my project before spending a lot time with what is going to be messy code I would go to the data source and see if there is a way to provide the information with the units and quantity as a separate field from the product or whatever.

It may be that whoever supplied this to you actually combined things from multiple fields into a single field thinking that was what was desired.

If you have an inventory data base floating around talk to the users or DB admins and see if there isn't a description separate from the packing units. It is very likely as many times you have "case" as well as "box" or "package" units which are handled in the inventory program to help when someone needs xxx of something.

Re: How to split a character variable

Ksharp — Wed, 12 Jun 2019 12:14:33 GMT

The following could give you a start.

data have;
input have $80. ;
want=prxchange('s/\S+\s*(Ea|Ct|Kg|Gr|Lt|Lb|Ml)\b//i',-1,have);
cards;
Sauce Alfredo #2 4/2 Kg	
Sauce Alfredo 2/3.7 Lt
Assist 1Al1 1/18.9 LT	
Assouplissant Solid Navisoft Bp S#Ncf# 2/6Lb
100% Whole Wheat 1/675 Gr	
15X20X1/2 White Cut/Board 6/1 Ea	
16Oz White Plastic Funnel 1/1 Ea	
A&W Diet Root Beer 12/355 Ml	
A&W Rootbeer Fridge Pack 12/355 Ml	
Almond Slivered Blanched (20067261010715) 2/1.50 Kg	
Almond Slivered Blanched 1/3 Kg	
Almond Whole Blanched (20067261010418) 2/1.50 Kg	
Bacon 14/16 Ct Center Cut 8.75 In Sliced 1/5 Kg
Bacon 16/18 Ct Applewood Smoked 1/5 Kg	
Bacon 16/18 Ct Center Cut 1/5 Kg	
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct
;

Re: How to split a character variable

Gladis6680 — Wed, 12 Jun 2019 12:36:12 GMT

Thank you for your advise, I have reached out to the source.

Re: How to split a character variable

Gladis6680 — Wed, 12 Jun 2019 12:37:43 GMT

Thank you so much !!! this will help me get started for sure

Re: How to split a character variable

Gladis6680 — Fri, 21 Jun 2019 15:39:14 GMT

Hi,

Also is it possible to put the quantity (what was deleted in the prxchange command) into another field?

For example:

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
A&W Diet Root Beer 12/355 Ml	12/355 Ml
A&W Rootbeer Fridge Pack 12/355 Ml	12/355 Ml
Almond Slivered Blanched (20067261010715) 2/1.50 Kg	2/1.50 Kg
Almond Slivered Blanched 1/3 Kg	1/3 Kg
Almond Whole Blanched (20067261010418) 2/1.50 Kg	2/1.50 Kg
Bacon 14/16 Ct Center Cut 8.75 In Sliced 1/5 Kg	1/5 Kg
Bacon 16/18 Ct Applewood Smoked 1/5 Kg	1/5 Kg
Bacon 16/18 Ct Center Cut 1/5 Kg	1/5 Kg
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct	1/200 Ct
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
Bar Granola Oats N' Honey Crunchy 6/12/46 Gr	6/12/46 Gr
Spoon Set Measure SS 6/1 Ea	6/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
Spoon Soup Medium Weight Polypro Dense (00620868514701) 1/1000 Ct	1/1000 Ct
Spoon Wooden 18 In W/Long Handle 1/1 Ea	1/1 Ea
Spoon Wooden Heavy Duty 16 In - 40.6 Cm 1/1 Ea (575386)	1/1 Ea
Spread Apple Raspberry 1/14 Kg	1/14 Kg

thank you for your help

Re: How to split a character variable

Ksharp — Sat, 22 Jun 2019 12:17:41 GMT

Bacon 16/18 Ct Center Cut 1/5 Kg	1/5 Kg
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct	1/200 Ct

Why 16/18 Ct in the first obs is not picked up ?

Re: How to split a character variable

Ksharp — Sat, 22 Jun 2019 12:37:32 GMT

The following could give a start.

data have;
infile cards dsd truncover;
input x : $80.;
pid=prxparse('/[\.\/\d]+\s*(Ea|Kg|Ct|Gr|Lt|Lb|Ml)\b/i');
call prxsubstr(pid,x,p,l);
if p then want=substr(x,p,l);
drop pid p l;
cards;
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
A&W Diet Root Beer 12/355 Ml	12/355 Ml
A&W Rootbeer Fridge Pack 12/355 Ml	12/355 Ml
Almond Slivered Blanched (20067261010715) 2/1.50 Kg	2/1.50 Kg
Almond Slivered Blanched 1/3 Kg	1/3 Kg
Almond Whole Blanched (20067261010418) 2/1.50 Kg	2/1.50 Kg
Bacon 14/16 Ct Center Cut 8.75 In Sliced 1/5 Kg	1/5 Kg
Bacon 16/18 Ct Applewood Smoked 1/5 Kg	1/5 Kg
Bacon 16/18 Ct Center Cut 1/5 Kg	1/5 Kg
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct	1/200 Ct
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
Bar Granola Oats N' Honey Crunchy 6/12/46 Gr	6/12/46 Gr
Spoon Set Measure SS 6/1 Ea	6/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
Spoon Soup Medium Weight Polypro Dense (00620868514701) 1/1000 Ct	1/1000 Ct
Spoon Wooden 18 In W/Long Handle 1/1 Ea	1/1 Ea
Spoon Wooden Heavy Duty 16 In - 40.6 Cm 1/1 Ea (575386)	 1/1 Ea
Spread Apple Raspberry 1/14 Kg
;
run;

Re: How to split a character variable

hashman — Sun, 23 Jun 2019 04:50:27 GMT

@Gladis6680:

Since your pattern is rather simple, surely it is possible, even without the sophistication of regexen. All you have to do is find the position of the blank before the last slash:

data have ;                                                               
  input have $80. ;                                                       
  cards;                                                                  
Sauce Alfredo #2 4/2 Kg                                                   
Sauce Alfredo 2/3.7 Lt                                                    
Assist 1Al1 1/18.9 LT                                                     
Assouplissant Solid Navisoft Bp S#Ncf# 2/6Lb                              
100% Whole Wheat 1/675 Gr                                                 
15X20X1/2 White Cut/Board 6/1 Ea                                          
16Oz White Plastic Funnel 1/1 Ea                                          
A&W Diet Root Beer 12/355 Ml                                              
A&W Rootbeer Fridge Pack 12/355 Ml                                        
Almond Slivered Blanched (20067261010715) 2/1.50 Kg                       
Almond Slivered Blanched 1/3 Kg                                           
Almond Whole Blanched (20067261010418) 2/1.50 Kg                          
Bacon 14/16 Ct Center Cut 8.75 In Sliced 1/5 Kg                           
Bacon 16/18 Ct Applewood Smoked 1/5 Kg                                    
Bacon 16/18 Ct Center Cut 1/5 Kg                                          
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct                             
run ;                                                                     
                                                                          
data want ;                                                               
  set have ;                                                              
  _n_ = findc (substr (have, 1, findc (have, "/", -32767)), " ", -32767) ;
  quantity = substr (have, _n_ + 1) ;                                     
  have     = substr (have,  1, _n_) ;                                     
run ;

Note that you cannot swap the order of the assignment statements for QUANTITY and HAVE without breaking the algorithm.

Kind regards

Paul D.

Re: How to split a character variable

hashman — Sun, 23 Jun 2019 04:54:27 GMT

@Ksharp:

Because the OP's pattern rule appears to be simpler than you might have thought. It is merely "find the last slash and split on the position of the first blank before it".

Kind regards

Paul D.

Re: How to split a character variable

Ksharp — Sun, 23 Jun 2019 12:20:41 GMT

Then SCAN() is more than suffice .

data have ;                                                               
  input have $80. ;                                                       
  cards;                                                                  
Sauce Alfredo #2 4/2 Kg                                                   
Sauce Alfredo 2/3.7 Lt                                                    
Assist 1Al1 1/18.9 LT                                                     
Assouplissant Solid Navisoft Bp S#Ncf# 2/6Lb                              
100% Whole Wheat 1/675 Gr                                                 
15X20X1/2 White Cut/Board 6/1 Ea                                          
16Oz White Plastic Funnel 1/1 Ea                                          
A&W Diet Root Beer 12/355 Ml                                              
A&W Rootbeer Fridge Pack 12/355 Ml                                        
Almond Slivered Blanched (20067261010715) 2/1.50 Kg                       
Almond Slivered Blanched 1/3 Kg                                           
Almond Whole Blanched (20067261010418) 2/1.50 Kg                          
Bacon 14/16 Ct Center Cut 8.75 In Sliced 1/5 Kg                           
Bacon 16/18 Ct Applewood Smoked 1/5 Kg                                    
Bacon 16/18 Ct Center Cut 1/5 Kg                                          
Bag Garbage 35X50 Heavy Duty Natural 1/200 Ct
; 
run ;                                                                     
                                                                          
data want ;                                                               
  set have ;    
  want=catx('/',scan(scan(have,-2,'/'),-1,' '),scan(have,-1,'/'));
run;

Re: How to split a character variable

hashman — Sun, 23 Jun 2019 17:00:19 GMT

@Ksharp:

To form the "quantity" part, which you name here WANT, - yes, it does (though I'd rather not leave it at the default length 200). However, it leaves HAVE uncut. Methinks to rid it of the trailing "quantity", determining the split position and making SUBSTR rely on it is still called for. In principle, using TRANWRD to replace the trailing "quantity" with blanks is possible, but it's iffy since the presence of the same substring in HAVE, though unlikely, cannot be ruled out.

Best regards

Paul D.

Re: How to split a character variable

Gladis6680 — Mon, 24 Jun 2019 20:32:06 GMT

Thank you so much !!! I have to do some manual parsing in order to the quantity, but this has definitely helped me!!!

Re: How to split a character variable

Gladis6680 — Mon, 24 Jun 2019 20:33:07 GMT

Thank you so much !!!

Re: How to split a character variable

Gladis6680 — Fri, 28 Jun 2019 13:12:54 GMT

Hi there,

I had another question regarding this. I have slightly modified this by just added the other units of measurement.

want=prxchange('s/\S+\s*(Ea|Ct|Kg|Gr|Lt|Lb|Ml|Ga|Oz|Fl|Ga|Each|L)\b//i',-1,have);

But then I get the below table, is it possible to make the above code case sensitive, meaning it only takes out 'Ea' and not 'ea'

I would appreciate your help, Thank you

HAVE	GETTING	WANT
Nestea Tea Cool Fridge Pack 12/355 Ml	Fridge Pack	Nestea Tea Cool Fridge Pack
Tea Traditional Herbal Variety Pack 6/28 Ct	Variety Pack	Tea Traditional Herbal Variety Pack
Tea Herbal Apple Cranberry 6/28 Ct	Apple Cranberry	Tea Herbal Apple Cranberry
Nestea Tea Green W/Lemon 12/500 Ml	Green W/Lemon	Nestea Tea Green W/Lemon
Crystal Apple Low Calorie (20058336320846) 2/500 Gr	Apple Low Calorie (20058336320846)	Crystal Apple Low Calorie (20058336320846)
Drink Juice Cocktail Cranberry 12/1 Lt	Drink Juice Cranberry	Drink Juice Cocktail Cranberry
Juice Cranberry Cocktail Plastic 24/300 Ml	Juice Cranberry Plastic	Juice Cranberry Cocktail Plastic
Juice Cranberry Cocktail Plastic Portion Cup (50058056200368) 96/114 Ml	Juice Cranberry Plastic Portion Cup (50058056200368)	Juice Cranberry Cocktail Plastic Portion Cup (50058056200368)
Juice Cranberry Cocktail Tetra Slim 12/1 Lt (10056412503629)	Juice Cranberry Tetra Slim (10056412503629)	Juice Cranberry Cocktail Tetra Slim (10056412503629)

Re: How to split a character variable

Ksharp — Fri, 28 Jun 2019 13:30:09 GMT

Remove ' i ' modification.

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

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

Re: How to split a character variable

Gladis6680 — Fri, 28 Jun 2019 14:00:56 GMT

thank you very much, that worked!!!!

Re: How to split a character variable

Gladis6680 — Fri, 28 Jun 2019 18:42:50 GMT

So sorry to ask so many questions, but I am not so familiar with the prxchange function. Is it possible to also take the information in the brackets with the number. I tried different possibilities, but its not working. Thank you very much

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

HAVE	WANT
Crystal Apple Low Calorie (20058336320846) 2/500 Gr	Crystal Apple Low Calorie
Juice Cranberry Cocktail Plastic Portion Cup (50058056200368) 96/114 Ml	Juice Cranberry Cocktail Plastic Portion Cup
Juice Cranberry Cocktail Tetra Slim 12/1 Lt (10056412503629)	Juice Cranberry Cocktail Tetra Slim