I have a dataset and want to calculate a ratio. Please see below for an illustration.
firm date dummy assets ratio
1 1 B 100 100/(100+10)
1 1 S 10
1 2 S 10 0/(10+0)=0
....
I have a dummy B/S, big or small. I want to calculate the ratio of big assets (I don't care about ratio of small assets because it's just a matter of 1- ). I want to use proc sql, but in this case, some firm-date observations don't have big assets (no B dummy), then I assume the ratio is 0. I think of the following code, but don't know if it makes sense. Please help me check. Thanks.
proc sql;
create table want as
select *, assets(where dummy="B")/sum(assets) as ratio
from have
group by firm, date;
quit;
data have;
input firm date dummy$ assets ;
cards;
1 1 B 100
1 1 S 10
1 2 S 10
;
proc sql;
create table want as
select *, case when dummy='B' then assets/sum(assets) else 0 end as ratio
from have
group by firm, date;
quit;
Close, if I understand properly what you want.
proc sql;
create table want as
select *, sum(case when dummy="B" then assets else 0 end)/sum(assets) as ratio
from have
group by firm, date;
quit;
data have;
input firm date dummy$ assets ;
cards;
1 1 B 100
1 1 S 10
1 2 S 10
;
proc sql;
create table want as
select *, case when dummy='B' then assets/sum(assets) else 0 end as ratio
from have
group by firm, date;
quit;
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.