Find control sample matching by year, industry and finding the nearest total asset

[gnesgne]

Hi,

I am experiencing some problem trying to find my control sample. Basically I have these data:

My treatment firms

year	Company	GICS	Total Asset
2001	A	403010	10000
2002	A	403010	11000
2003	A	403010	12000
2004	A	403010	13000
2005	A	403010	14000
2001	B	402010	20000

My control sample

year	Company	GICS	Total Asset
2001	A	403010	10230
2001	A	403010	116570
2001	A	403010	10200
2001	A	403010	13000
2001	A	403010	14000
2001	B	402010	20000

Basically i want to find the nearest total asset with two criteria, matching the year followed by the GICS code. Any help will be greatly appreciated

Thanks in advance.

[Ksharp]

If your table is not big.

Code: Program

data treatment;
infile cards truncover expandtabs;
input year Company $ GICS TotalAsset;
cards;
2001 A 403010 10000
2002 A 403010 11000
2003 A 403010 12000
2004 A 403010 13000
2005 A 403010 14000
2001 B 402010 20000
;
run; 

data control;
infile cards truncover expandtabs;
input year Company $ GICS TotalAsset;
cards;
2001 A 403010 10230
2001 A 403010 116570
2001 A 403010 10200
2001 A 403010 13000
2001 A 403010 14000
2001 B 402010 20000
;
run;
proc sql;
 create table want as 
  select a.*,b.TotalAsset as new_TotalAsset
   from treatment as a left join  control as b
   on a.company=b.company and a.gics=b.gics and a.year=b.year
   group by a.company,a.gics,a.year
   having abs(a.TotalAsset-b.TotalAsset)=min(abs(a.TotalAsset-b.TotalAsset));
quit;

[HarryLiu]

data treatment;

input year company $ GICS totalasset;

cards;

2001 A 403010  10000

2002 A 403010 11000

2003 A 403010 12000

2004 A 403010 13000

2005 A 403010 14000

2001 B 402010 20000

;

run;

data control;

input year company $ GICS totalasset;

cards;

2001 A 403010  10230

2001 A 403010 116570

2001 A 403010 10200

2001 A 403010 13000

2001 A 403010 14000

2001 B 402010 20000

;

run;

proc sql;

create table final as

select treatment.*, control.totalasset as new_totalasset,(treatment.totalasset-control.totalasset) as diff

from treatment left join control

on treatment.year=control.year and treatment.GICS=control.GICS

where treatment.year=control.year and treatment.GICS=control.GICS

group by treatment.year, treatment.GICS

having abs(diff)=min(abs(diff));

quit;

proc print;

run;