Help with a proc sql question

[SeanZ]

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;

[snoopy369]

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;

[Linlin · Accepted solution]

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;