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.
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 .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.