Help using Base SAS procedures

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

Reply
N/A
Posts: 1

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

Hi,

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

My treatment firms

yearCompanyGICSTotal Asset
2001A

403010

10000
2002A40301011000
2003A40301012000
2004A40301013000
2005A40301014000
2001B40201020000

My control sample

yearCompanyGICSTotal Asset
2001A

403010

10230
2001A403010116570
2001A40301010200
2001A40301013000
2001A40301014000
2001B40201020000

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.

Super User
Posts: 10,041

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

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;
Contributor
Posts: 23

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

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;

Ask a Question
Discussion stats
  • 2 replies
  • 220 views
  • 0 likes
  • 3 in conversation