topic Re: replace part of variable in SAS Programming

replace part of variable

France — Tue, 20 Nov 2018 13:45:27 GMT

Dear all,

I would like to replace “LIMITED” with “LTD”, “SOCIETE ANONYME” with “SA”.

NAME
DYNAMIC COMMERCIAL FINANCE PLC,
MTR CORPORATION LIMITED,
INTERCEDE GROUP SOCIETE ANONYME

NAME
DYNAMIC COMMERCIAL FINANCE PLC,
MTR CORPORATION LTD,
INTERCEDE GROUP SA

could you please give me some suggestion about it?

thanks in advance

Re: replace part of variable

RW9 — Tue, 20 Nov 2018 13:48:23 GMT

Tranwrd:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000215027.htm

Perl regular expressions. Findw/substr. Other string functions.

Re: replace part of variable

Reeza — Tue, 20 Nov 2018 15:41:38 GMT

If your data set has an identifier for companies (BN, ID) use that instead. It's almost impossible to get the fuzzy matching for business names cleanly.

Re: replace part of variable

error_prone — Tue, 20 Nov 2018 15:49:22 GMT

What do you expect as result of name is
Limited Luxurious Lending Incorporated
?

It is hardly possible - at least not without a larger amount of code - to produce the expected result.

Re: replace part of variable

France — Tue, 20 Nov 2018 15:51:58 GMT

Dear RW9,

thank you for your advice.

I have tried to use,

data sa_step3.Data_UK_companies_try1;
set sa_step3.Datastream_UK_public_companies;
NAME1=tranwrd(COMPANY_NAME, "LIMITED", "LTD");
NAME1=tranwrd(COMPANY_NAME, "SOCIETE ANONYME", "SA");
put NAME1;
run;

However, I did not get the variable. Could you please give some suggestions in detail.

thanks in advance.

Re: replace part of variable

France — Tue, 20 Nov 2018 15:57:45 GMT

Dear Reeza,

thanks for your kind advice.

what I am doing is to match two databases by the companies' name (PATSTAT database and Datastream database). I did not find any identifier for both of them yet. so I have to standardise the companies' name and match them together. is there any method I can use to match successfully.

Re: replace part of variable

RW9 — Tue, 20 Nov 2018 16:00:08 GMT

Its really not possible to answer from "However, I did not get the variable. ". What didn't happen? Post test data in the form of a datastep - this is so we can see what your working with. The code:

data temp;
  company_name="Somewhere LIMITED company";
  name1=tranwrd(company_name,"LIMITED","LTD");
  name1=tranwrd(name1,"SOCIETE ANONYME","SA");
run;

Works fine. Note that the second tranwrd should use the name1 variable and from there on in.

Re: replace part of variable

France — Tue, 20 Nov 2018 16:08:44 GMT

Dear error_prone,

thanks for your helpful advice.

is there any method I can use to only replace the last 'LIMITED' as 'LTD'.

Re: replace part of variable

Reeza — Tue, 20 Nov 2018 16:10:23 GMT

@France wrote:
Dear Reeza,

thanks for your kind advice.

what I am doing is to match two databases by the companies' name (PATSTAT database and Datastream database). I did not find any identifier for both of them yet. so I have to standardise the companies' name and match them together. is there any method I can use to match successfully.

Not really. I tried this last year and the best we could do was about 60% and that was after cleaning up all the LTD, CO and such. Company names are ugly and convoluted and they have too many subsidiaries with similar names to be reliable when matched.

Re: replace part of variable

mansour_ib_sas — Tue, 20 Nov 2018 16:31:14 GMT

Hello,

I make this solution with macro

option symbolgen mprint mlogic;

DATA test;
infile cards dsd truncover;
INPUT subject $100.  ;
CARDS;
NAME
DYNAMIC COMMERCIAL FINANCE PLC 
MTR CORPORATION LIMITED
INTERCEDE GROUP SOCIETE ANONYME
;
run;

%let var_have =LIMITED,SOCIETE ANONYME ;
%let var_change=LTD ,SA ;



%macro test();
data test;
set test;
%let i=1;
%do %while(%length(%scan(%quote(&var_have), &i, %str(,)))>0);
subject=tranwrd(subject, scan("&var_have",&i, ',') , scan("&var_change",&i, ',' ) );

%let i=%eval(&i+1);
%end;
run;
%mend test;

%test;