topic Re: Extract key word from a composed reference in SAS Programming

Extract key word from a composed reference

Marwa_Se — Thu, 20 Sep 2018 14:30:14 GMT

Hello!

I spent the whole day looking for solution for my problem but with no issue

I have a set of data which contains operations bank, i would like to extract only the name of the compagny or the business service

it's very complicated because the operation name is a reference with lot of numbers and characters

For example:

operations

0123456789 2547 IKEA

5874521 265935 ORANGE ASSUR 254 147

77745812 MERCEDES 25477

257CARREFOUR15 1254 774

i would like to obtain a colum like this bellow:

Result

IKEA

ORANGE ASSUR

MERCEDES

CARREFOUR

Seriously the person who will find the solution for this would be a real Genius !!

Thank you in advance guys !!

Re: Extract key word from a composed reference

novinosrin — Thu, 20 Sep 2018 14:40:41 GMT


data have;
input var $50.;
cards;
0123456789 2547 IKEA
5874521 265935 ORANGE ASSUR 254 147
77745812 MERCEDES 25477
257CARREFOUR15 1254 774
;

data want;
set have;
want=strip(compress(var,' ','kai'));
run;

Re: Extract key word from a composed reference

ErikLund_Jensen — Thu, 20 Sep 2018 14:55:53 GMT

The easy way is to use prxchange to divide the string in tree elements, digits and blanks before first char, all chars and blanks up to first digit after, and the remaining digits/blanks, and drop

data have;
	input var $char80.;
datalines;
0123456789 2547 IKEA
5874521 265935 ORANGE ASSUR 254 147
77745812 MERCEDES 25477
257CARREFOUR15 1254 774
;
run;
data want; set have; 
	length want $30;
	want = prxchange('s/([\d|\s]*)(\D*)([\d|\s]*)/$2/',-1,var);
	put want;
run;

IKEA
ORANGE ASSUR
MERCEDES
CARREFOUR

the first and third element. The supplied code works with your data, but there might be combinations it cannot handle..

Re: Extract key word from a composed reference

Marwa_Se — Thu, 20 Sep 2018 14:59:16 GMT

Thank u a lo, it works but not for the whole references ,

I thought that was related to the length because i can't obtain the whole name

for example

for this reference :

CARD BANK 000170999200

i obtained ony : CARD

i added format var $100.;

but it doesn't work

Re: Extract key word from a composed reference

ErikLund_Jensen — Thu, 20 Sep 2018 15:03:44 GMT

That is weird. If I add CARD Bank, it works fine:

data have;
input var $char80.;
datalines;
0123456789 2547 IKEA
5874521 265935 ORANGE ASSUR 254 147
77745812 MERCEDES 25477
257CARREFOUR15 1254 774
CARD BANK 000170999200
;
run;
data want; set have;
length want $30;
want = prxchange('s/([\d|\s]*)(\D*)([\d|\s]*)/$2/',-1,var);
put want;
run;

IKEA
ORANGE ASSUR
MERCEDES
CARREFOUR
CARD BANK

Re: Extract key word from a composed reference

Marwa_Se — Thu, 20 Sep 2018 15:07:18 GMT

thank u so much, but it's very strange even your method doesn't work for some references

i must have a problem with my database, i will focus ...

Thank you again for your help

Re: Extract key word from a composed reference

ballardw — Thu, 20 Sep 2018 16:31:19 GMT

@Marwa_Se wrote:

thank u so much, but it's very strange even your method doesn't work for some references

i must have a problem with my database, i will focus ...

Thank you again for your help

Realizing that the example data you provided may not exactly match your real data you might provide as much of an example from your "real" data that has problems.

Some issues might be from encoding or possibly characters from the extended alphabet.

Re: Extract key word from a composed reference

Marwa_Se — Fri, 21 Sep 2018 07:20:03 GMT

Yes i adapted my database to the method and now it works perfectly