topic Data matching- only one firm is chosen to be matched against the original dataset in SAS Procedures

Data matching- only one firm is chosen to be matched against the original dataset

mei — Tue, 28 Oct 2014 07:44:28 GMT

I have 2 datasets, one is small sample of 741 firms and I need to find a match for each firm from a dataset of 8330 firms using criteria of same financial year (fyear) and same industry (ffind) and take the firm with approximately the same size.

I have used the following sas codes to select matched pair. Now i found out that a same firm can be selected several times as a matched pair for the original dataset of 741 firms. How do I avoid the same firm to be selected again?

proc sort data=huang_741a;

by gvkey fyear;

run;

proc sort data=huang_bal_04a;

by gvkey fyear;

run;

proc sql;

create table control_741 as

select O.*, A.gvkey as Anum, A.fyear as Afy, abs(O.size-A.size) as sizeDiff

from huang_bal_04a as O inner join huang_741a as A

on O.fYear=A.fyear and O.ffind=A.ffind

where O.gvkey not in (select gvkey from huang_741a)

order by gvkey;

*27001 rows**;

proc sort data=control_741;

by anum afy;

run;

proc means data=control_741 noprint;

by Anum afy;

output out=selected_741 idgroup(min(sizeDiff) out[1] (fyear gvkey)=);

run;

**741**;

proc sql;

create table selected_741A as

select a.*, b.*

from selected_741 a left join huang_bal_04a b

on a.gvkey=b.gvkey and a.fyear=b.fYear

order by a.gvkey, a.fyear;

run;

**597**;

data combined_741;

set huang_741a selected_741A;

run;

**741+741=1282**;

Re: Data matching- only one firm is chosen to be matched against the original dataset

LearnByMistk — Tue, 28 Oct 2014 09:23:21 GMT

can u give some data sample and try to explain with help of that what you need.

Re: Data matching- only one firm is chosen to be matched against the original dataset

RW9 — Tue, 28 Oct 2014 09:52:29 GMT

Hi,

Well, in answer to your question "How do I avoid the same firm to be selected again?", that's a fairly simple response, look at your data, isolate why more than one record matches based on your join criteria, then evaluate a method to reduce that, maybe using distinct, or first date or something else. So look at each step and identify why you get multiple records would be the first stage. Providing example data, and narrowing down which procedure is causing the problem would be beneficial.

Re: Data matching- only one firm is chosen to be matched against the original dataset

mei — Tue, 28 Oct 2014 10:07:27 GMT

I have attached two files: huang_741 and huang_bal_04a,

I have now created 5 sets of matched pair for each firms using the codes as follows:

proc means data=control_total741 noprint;

by Anum afy;

output out=selected_total741_5set idgroup(min(sizeDiff) out[5] (fyear gvkey)=);

run;

**741**;

I have attached the file: selected_741_5sets.

I was thinking maybe it is good to look at this file. If 1 firms are selected three times, gvkey_1 will repeat for three times. then i should choose gvkey_2 and gvkey_3 for the second and third duplicates. Instead of going through manually (there are 71 duplicates), do you think is it a good way to write some codes to tell the program to choose correctly?

Thanks for your time.

Re: Data matching- only one firm is chosen to be matched against the original dataset

Ksharp — Tue, 28 Oct 2014 11:31:56 GMT

It would be better to post a little sample data and the output you need . Explain it as few words as you could.

Re: Data matching- only one firm is chosen to be matched against the original dataset

drsurf — Thu, 22 Oct 2015 14:57:07 GMT

It would be helpful if you also attached the huang_bal_04a dataset.