Create Columns from text

umeshgiri48 — Thu, 05 Mar 2020 14:03:34 GMT

I have a dataset in which I am trying to create multiple columns from one column basis on text for eg:-

Date	Customer	Address	Vch Type	Vch No.	Debit	Narration
03.04.2019	Hasan pvt ltd	Plot no 05, Tilak road New delhi, Delhi	Sales	BPL/AT/001	2336374.00	Being the Sale of Cut Ornge Grade:"APTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.
03.04.2019	Indra pvt ltd	112, Nelson road, Delhi	Sales	BPL/OT/002	3336374.00	Being the Sale of Cut Apple Grade:"ORTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.
03.04.2019	Middle Venture	5th Floor, Tamouh Tower, Marina Square, Al Reem Island Abu Dhabi United Arab Emirate	Sales	BPL/PT/003	2436375.00	Being the Sale of Cut Pineapple Grade:"PATC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.

now on the basis of narration I am trying to create new column for eg:-

Date	Customer	Address	Vch_Type	Vch_No	Debit	Narration	Grade	Invoice	qty	Amount	Exch_Rate
03.04.2019	Hasan pvt ltd	Plot no 05, Tilak road New delhi, Delhi	Sales	BPL/AT/001	2336374.00	Being the Sale of Cut Ornge Grade:"APTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.	APTC/I/66	BPL/CA/001/2019-20	12430 Kgs	$.34182.50.00	Rs.68.35
03.04.2019	Indra pvt ltd	112, Nelson road, Delhi	Sales	BPL/OT/002	3336374.00	Being the Sale of Cut Apple Grade:"ORTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.	ORTC/I/66	BPL/CO/001/2019-20	12430 Kgs	$.34182.50.00	Rs.68.35
03.04.2019	Middle Venture	5th Floor, Tamouh Tower, Marina Square, Al Reem Island Abu Dhabi United Arab Emirate	Sales	BPL/PT/003	2436375.00	Being the Sale of Cut Pineapple Grade:"PATC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.	PATC/I/66	BPL/CP/001/2019-20	12430 Kgs	$.34182.50.00	Rs.68.35

I have tried using substr and scan function but it not giving me the result because words count and delimiter's are not same

Please help me on this.

Re: Create Columns from text

PaigeMiller — Thu, 05 Mar 2020 15:28:23 GMT

Find where the text Grade: is in the variable called Narration, then take the next word where the delimiters are double quotes

Example

where = findw(narration,'Grade:');
grade = scan(substr(narration,where+6),1,'"');

Re: Create Columns from text

Duggins — Thu, 05 Mar 2020 19:02:48 GMT

I had effectively the same approach as above, but I would probably nest all the position information unless you need to save that information. The ones below should work as long as the data you have follows the style shown in your original post. I did use two steps for Exchange Rate so that the code wasn't as wide when posting it.

grade = scan(substr(narration, index(narration,'Grade:')+6),1,'"');
invoice = scan(substr(narration, index(narration,'No.')+3),1,' ');
qty = input(scan(substr(narration, index(narration,'Qty.')+4),1,' '),best12.);
Amount = scan(substr(narration,index(narration,'$')),1,' ');
_ER = scan(substr(narration, index(narration,'Rate')+4),1,':');
ExchRate = substr(_ER,1,length(_ER)-1);

topic Re: Create Columns from text in New SAS User

Create Columns from text

Re: Create Columns from text

Re: Create Columns from text