clean the company name

[France]

dear all

I face trouble when I clean the company name by using following code to exclude the PLC. etc.

data Step3.Datastream_gb_export_2nd;
set Step5.Datastream_gb2;
call scan(COMPANY_NAME, -1, position, length);
if scan(COMPANY_NAME,-1) in ('LTD' ,'LTD.' ,'LIMITED' ,'PLC' ,'PLC.' ,'AG' ,'S.A.' ,'SA' ,'corporation' ,'CORP.' 'CORP' ,'INC.' ,'INC' ,'COMPANY') then substr(COMPANY_NAME,position)=' ';
drop position length;
run;
data Step3.Datastream_gb_export_2nd;
set Step3.Datastream_gb_export_2nd;
call scan(COMPANY_NAME_1, -1, position, length);
if scan(COMPANY_NAME_1,-1) in ('LTD' ,'LTD.' ,'LIMITED' ,'PLC' ,'PLC.' ,'AG' ,'S.A.' ,'SA' ,'corporation' ,'CORP.' 'CORP' ,'INC.' ,'INC' ,'COMPANY') then substr(COMPANY_NAME_1,position)=' ';
drop position length;
run;
data Step3.Datastream_gb_export_2nd;
set Step3.Datastream_gb_export_2nd;
call scan(COMPANY_NAME___SHORT, -1, position, length);
if scan(COMPANY_NAME___SHORT,-1) in ('LTD' ,'LTD.' ,'LIMITED' ,'PLC' ,'PLC.' ,'AG' ,'S.A.' ,'SA' ,'corporation' ,'CORP.' 'CORP' ,'INC.' ,'INC' ,'COMPANY') then substr(COMPANY_NAME___SHORT,position)=' ';
drop position length;
run;
data Step3.Datastream_gb_export_2nd;
set Step3.Datastream_gb_export_2nd;
call scan(PREVIOUS_NAME, -1, position, length);
if scan(PREVIOUS_NAME,-1) in ('LTD' ,'LTD.' ,'LIMITED' ,'PLC' ,'PLC.' ,'AG' ,'S.A.' ,'SA' ,'corporation' ,'CORP.' 'CORP' ,'INC.' ,'INC' ,'COMPANY') then substr(PREVIOUS_NAME,position)=' ';
drop position length;
run;

I did not exclude all of them successfully. For example 'S.A.'  'Corporation'. I have added the source excel( Step5.Datastream_gb2) and result excel (Step3.Datastream_gb_export_2nd) in the attachments.

Could you please give me some suggestions?

thanks in advance.

[34reqrwe]

the scan function by default would see any of these as delimiters blank ! $ % & ( ) * + , - . / ; < ^ :

So the last "word" of a string ending S.A. is not going to be S.A.  as that is 2 words.  

try creating a new column in your dataset that contains the result of the scan function and you can see

new=scan(COMPANY_NAME,-1)

You also have not specfied 'Corporation'  - but 'corporation' is there . 

Have a look at using tranwrd/trantrn for this as it may save you some time .