clean the company name based on the country code

[France]

dear all,

how can I exclude the specific character for all four variables (i.e., COMPANY_NAME, COMPANY_NAME_1, COMPANY_NAME___SHORT, PREVIOUS_NAME) based on the country code

for the following excel.

COMPANY_NAME	COMPANY_NAME_1	COMPANY_NAME___SHORT	PREVIOUS_NAME	GEOGRAPHIC_DESCR_
NORSK HYDRO ASA	NORSK HYDRO ASA	NORSK HYDRO ASA	NORSK HYDRO ADR (XSQ)	NORWAY
SIRTI SPA	SIRTI SPA	SIRTI SPA	SIRTI (XSQ)	ITALY
EVC INTERNATIONAL NV	EVC INTERNATIONAL NV	EVC INTERNATIONAL NV	EVC INTL. (XSQ)	NETHERLANDS
CARDO AB	CARDO AB	CARDO AB	CARDO (XSQ)	SWEDEN
HASLEMERE NV	HASLEMERE NV	HASLEMERE NV		NETHERLANDS
IRO AB	IRO AB	IRO AB	IRO (XSQ)	SWEDEN
ALLEANZA ASSICURAZIONI S.P.A.	ALLEANZA	ALLEANZA ASSICUR.		ITALY
POLYGRAM N.V.	POLYGRAM	POLYGRAM N.V.		NETHERLANDS
HEINEKEN NV	HEINEKEN NV	HEINEKEN N.V.		NETHERLANDS
SAMPO OYJ	SAMPO OYJ	SAMPO OYJ	SAMPO 'A' (XSQ)	FINLAND

I expect the exclude the specific character if

1) the character is the last character of a 'company name' (i.e., all of these four variables, which are COMPANY_NAME, COMPANY_NAME_1, COMPANY_NAME___SHORT, PREVIOUS_NAME) text.

2) it is 'AB' if GEOGRAPHIC_DESCR_ is SWEDEN or Finland,

it is 'SPA' or 'S.P.A' if GEOGRAPHIC_DESCR_ is ITALY,

it is 'NV' or 'N.V' if GEOGRAPHIC_DESCR_ is Netherlands or Belgium or Indonesia or Suriname,

it is 'ASA' if GEOGRAPHIC_DESCR_ is NORWAY,

it is 'OYJ' if GEOGRAPHIC_DESCR_ is FINLAND.

Could you please give me some suggestions about this.

thanks in advance.

[ChrisNZ]

Like this?

data NEW;
  COMPANY_NAME   ='POLYGRAM N.V.'  ;
  COMPANY_NAME_1 ='POLYGRAM     '  ;
  GEOGRAPHIC_DESC='NETHERLANDS  '  ;
  if GEOGRAPHIC_DESC='NETHERLANDS' then do;
    COMPANY_NAME   = prxchange('s/(NV|N\.V\.)$//',1,trim(COMPANY_NAME  ));
    COMPANY_NAME_1 = prxchange('s/(NV|N\.V\.)$//',1,trim(COMPANY_NAME_1));
  end;
run;

COMPANY_NAME	COMPANY_NAME_1	GEOGRAPHIC_DESC
POLYGRAM	POLYGRAM	NETHERLANDS

[ChrisNZ · Accepted solution]

If you prefer to use the scan function:

data NEW;
  COMPANY_NAME   ='POLYGRAM N.V.'  ;
  COMPANY_NAME_1 ='POLYGRAM     '  ;
  GEOGRAPHIC_DESC='NETHERLANDS  '  ;
  if GEOGRAPHIC_DESC='NETHERLANDS' then do;
    call scan(COMPANY_NAME, -1, POSITION, LENGTH,' ');
    if scan(COMPANY_NAME,-1,' ') in ('NV','N.V.') then substr(COMPANY_NAME,POSITION)=' ';
    call scan(COMPANY_NAME_1, -1, POSITION, LENGTH,' ');
    if scan(COMPANY_NAME_1,-1,' ') in ('NV','N.V.') then substr(COMPANY_NAME_1,POSITION)=' ';
  end;
run;