topic Re: Help with a proc sql question in SAS Procedures

Help with a proc sql question

SeanZ — Wed, 30 Oct 2013 22:30:06 GMT

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;

Re: Help with a proc sql question

snoopy369 — Wed, 30 Oct 2013 23:05:29 GMT

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;

Re: Help with a proc sql question

Linlin — Wed, 30 Oct 2013 23:20:26 GMT

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;