clean the company name

France — Wed, 16 Jan 2019 01:07:02 GMT

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.

Re: clean the company name

34reqrwe — Wed, 16 Jan 2019 01:48:13 GMT

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 .

topic clean the company name in SAS Programming

clean the company name

Re: clean the company name