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 .
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: