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.
If your table is not big.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.