topic Re: match two data sets with company names in SAS Programming

match two data sets with company names

Songchan — Sat, 05 Jan 2019 19:58:39 GMT

Hi guys,

I'm trying to merge two data sets by ticker symbols, but ticker symbols are not unique to every company, they can be reused by other companies, so I also need to merge two data sets by company names, The problem is names for same company in different data sets may be different in formats (such as up or lower case, inc. or corp, with "-" or " " in two words), how could I solve this problem? Please have a look of below codes

Kind regards,

Songchan

proc sql;
     create table temp03 as
	 select distinct a.*, b.permno, b.comnam, b.ticker, b.date
	 from temp02 as a 
	      left join temp01a as b
		  on a.ticker=b.ticker;
quit;

Re: match two data sets with company names

PeterClemmensen — Sat, 05 Jan 2019 20:16:28 GMT

Can you provide some sample data for us to work with?

Re: match two data sets with company names

Songchan — Sat, 05 Jan 2019 20:39:53 GMT

Hi, thanking for replying me. Please see attached documents

Re: match two data sets with company names

PeterClemmensen — Sat, 05 Jan 2019 21:03:11 GMT

.xlsx files are not too good for sample data. However, there are a few ways to do this.

You could use the COMPGED or COMPLEV Functions. These are functions that compare strings and return some numeric constant of the 'distance' between the two strings. The smaller the returned constant, the more the two strings look alike.

Re: match two data sets with company names

kiranv_ — Sat, 05 Jan 2019 21:08:48 GMT

if everytime it is last word is one different then you can something like this may work

proc sql;
     create table temp03 as
	 select distinct a.*, b.permno, b.comnam, b.ticker as lookupticker, b.date
	 from temp02 as a 
	      left join temp01a as b
		  on upcase(scan(a.ticker,-1))=upcase(scan(b.ticker,-1))
		;  
quit;

Re: match two data sets with company names

Songchan — Sat, 05 Jan 2019 21:10:09 GMT

Hi,

What kind of sample data you wanna see? Because it's very hard for me to find which ticker has several companies to corresponded.

Re: match two data sets with company names

Songchan — Sat, 05 Jan 2019 21:17:49 GMT

Thank you for your reply,

I need two conditions now, one is with the same ticker, another is with similar company names (because i have to make sure they stand for the same company), the company names vary in many ways ( up or lower case, "blank between two words" or use "-")

Re: match two data sets with company names

PeterClemmensen — Sat, 05 Jan 2019 21:20:25 GMT

@kiranv_s code can be edited to look something like this. Does the logic that he describes work for you?

proc sql;
     create table temp03 as
	 select distinct a.*, b.permno, b.comnam, b.ticker as lookupticker, b.date
	 from temp02 as a 
	      left join temp01a as b
         on a.ticker=b.ticker
		   and upcase(scan(a.name,-1))=upcase(scan(b.name,-1))
		;  
quit;

Re: match two data sets with company names

Songchan — Sat, 05 Jan 2019 21:32:46 GMT

It works to some extents, but still not a good match. I think maybe there is no better way.

Re: match two data sets with company names

kiranv_ — Sat, 05 Jan 2019 21:36:42 GMT

As @PeterClemmensen explains a sample will be helpful, otherwise it would be difficult. It made a template for you. Just try to make different ways you can see data will be helpful. And also mention how your output should be from your input datasets

data temp02;
input ticker $  1-7  anotherval;
datalines;
AB INC 100
AC INC 200
;

data temp01a;
input ticker $ 1-7  anotherval1 9-11;
datalines;
AB INC  500
AC INC  200
AB      200
AD CORP 700
;

Re: match two data sets with company names

Songchan — Sat, 05 Jan 2019 21:49:49 GMT

data temp02;
input ticker $  1-7  anotherval;
datalines;ABC INC   ABC
AC INC    AC
A D INC   AD;

data temp01a;
input ticker $ 1-7  anotherval1 9-11;
datalines;abc inc   ABC
AC  corp  AC
A-D corp  AD;
the first column is company name, the second is ticker symbol. The result i desire is:

ABC INC   ABC   abc inc
AC INC    AC    AC corp
A D INC   AD    A-D corp

Another company names are various in someways, but they are the same companies and having the same ticker symbols

Re: match two data sets with company names

novinosrin — Sat, 05 Jan 2019 22:05:28 GMT

Hi @Songchan I'm afraid none of the suggestions will work for this scenario

inc. or corp, This is really messy

with "-" or " " in two words) - This can be handled

Can you list all the possible messy stuff or is that all?

If this all, no biggie, but i seriously doubt if that's the case

Re: match two data sets with company names

Reeza — Sat, 05 Jan 2019 22:21:34 GMT

Isn’t there a data set somewhere that has ticker symbols and the dates they belong to a particular company? That would likely be a better way. Also, you’re going to miss name changes, same ticker but company rebrands under a different name. That’s more likely to occur IME than finding companies reusing the same symbol over time.

I would do a distance calc like suggested and take the nearest match.

For sample data, please try this, i know it’s not perfect but it’s a lot easier for people to work with. You should try to include as many variations of the issue as you can.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

@Songchan wrote:

Hi guys,

I'm trying to merge two data sets by ticker symbols, but ticker symbols are not unique to every company, they can be reused by other companies, so I also need to merge two data sets by company names, The problem is names for same company in different data sets may be different in formats (such as up or lower case, inc. or corp, with "-" or " " in two words), how could I solve this problem? Please have a look of below codes

Kind regards,

Songchan
proc sql;
     create table temp03 as
	 select distinct a.*, b.permno, b.comnam, b.ticker, b.date
	 from temp02 as a 
	      left join temp01a as b
		  on a.ticker=b.ticker;
quit; 

Re: match two data sets with company names

kiranv_ — Sat, 05 Jan 2019 23:05:37 GMT

Following things might help.

1. you should have lookup table for this as suggested by @Reeza or you need to make a lookup table(with help of business users) and include new variations whenever you have new variations.

2. In absence of lookup table you need to have a program( small ETL code) which address this problem(business people might be able to help on this) to tackle issues. your code should also should updates on regular basis as per business users

Otherwise It is almost impossible to fix this kind of issues.

3. Most importantly you need to sit with your business users and if possible define the rules along with them to handle the data. It would be easier if you can get some help from business users on this. Mostly senior members/business analysts in your Team will have good idea on this kind of things.

Re: match two data sets with company names

Songchan — Sat, 05 Jan 2019 23:24:23 GMT

Hi, thank you for your reply. Yes, that's all.

Re: match two data sets with company names

mkeintz — Sun, 06 Jan 2019 18:37:08 GMT

I am not going to suggest sas code to you, because I don't propose to convert your excel sheets into a sas data step for use in this forum. However, if you do so, I'd be glad to give it a go.

I see the first spreadsheet is CRSP data (id variable PERMNO) and it includes a single DATE variable. And the other data has a NAME and TICKER, together with a STARTDATE and ENDDATE. In the other data set, is the specified ticker in use by that company for the entire date range from STARTDATE to ENDDATE? If so then match on ticker conditional on the CRSP date falling between the startdate and enddate of the other data set.

Now if TICKER in the other data set only represents the status on ENDDATE, then note that you can go back to the CRSP data and make a ticker date range. CRSP publishes another data set, NAMES, which includes events such as ticker change. This would allow you to determine the full crsp date range for a ticker (say TICKERSTART and TICKEREND) that includes the date you already have in your CRSP data. Then you could match on ticker, condition on the ENDDATE from the other dataset falling between TICKERSTART and TICKEREND.

Then you would have all the easy matches.

Edit, due to unnoticed cut and paste result:

which allows you to build a date range for a ticker belonging to a particular PERMNO. Then you could do the same as above, but match on ticker, conditional on the ENDDATE of the other data set falling between the start and end of a ticker in CRSP.

that CRSP publishes other datasets, including a "names" dataset which will allow you to construct the actual time span a given ticker is assigned to a given PERMNO. So you could enhance your CRSP data to include a ticker_start_date and ticker_end_date. Then match on ticker, without having to struggle with trying to standardize company names.

Re: match two data sets with company names

PGStats — Sat, 05 Jan 2019 23:46:52 GMT

Here is what I tried, using complev. Seems to work pretty well. You might have to experiment with the best distance limit (10 in this example)

libname t01 excel "&sasforum.\datasets\temp01a.xlsx";
libname t02 excel "&sasforum.\datasets\temp02.xlsx";

proc sql;
create table t01 as select * from t01.temp01a;
create table t02 as select * from t02.temp02;
quit;

proc sql;
create table names as
select ticker, upcase(comnam) as name from t01 
union
select ticker, upcase(name) as name from t02
order by ticker;
quit; 

data nameEqv;
array n{10} $60;
array e{10} $60;

do i = 1 by 1 until(last.ticker);
    set names; by ticker;
    n{i} = name;
    e{i} = name;
    do j = 1 to i-1;
        if complev(n{j}, n{i}, 10) < 10 then do;
            e{i} = e{j};
            leave;
            end;
        end;
    eqvName = e{i};
    output;
    end;

keep ticker name eqvName;
run;

proc sql;
create table t03 as
select 
    A.*,
    upcase(B.name) as otherName,
    B.issuerDescription,
    B.startDate,
    B.endDate
from 
    (select T01.*, n01.eqvName 
        from 
            T01 inner join 
            nameEqv as n01 
                on T01.ticker=n01.ticker and upcase(T01.comnam)=n01.name) as A inner join
    (select T02.*, n02.eqvName 
        from 
            T02 inner join 
            nameEqv as n02 
                on T02.ticker=n02.ticker and upcase(T02.name)=n02.name) as B
        on A.ticker=B.ticker and A.eqvName=B.eqvName;
quit;

Re: match two data sets with company names

Songchan — Sun, 06 Jan 2019 00:03:36 GMT

Thank you for your reply. I never used macro before, could you explain what do the first two rows mean (i.e. what "&sasforum.\datasets "stands for) ?

Re: match two data sets with company names

PGStats — Sun, 06 Jan 2019 05:19:09 GMT

The two libname statements and the first proc SQL step bring the data from your Excel files into two datasets : t01 and t02. The real work starts on the second proc SQL step where a list of all distinct ticker-name combinations from both datasets is assembled.

Re: match two data sets with company names

Songchan — Sun, 06 Jan 2019 15:41:44 GMT

Thank you PG! That works! there are about 1,600s out of 2,889 observations matched. For the unmatched ones, do i have to match them manually or there is a better way?