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;
proc sort data=huang_bal_04a;
by gvkey fyear;
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;
proc sort data=control_741;
by anum afy;
proc means data=control_741 noprint;
by Anum afy;
output out=selected_741 idgroup(min(sizeDiff) out (fyear gvkey)=);
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;
set huang_741a selected_741A;
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 (fyear gvkey)=);
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.