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.
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;
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 |
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;
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: