## Help with a proc sql question

Solved
Frequent Contributor
Posts: 122

# Help with a proc sql question

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;

Accepted Solutions
Solution
‎10-30-2013 07:20 PM
Super Contributor
Posts: 1,636

## Re: Help with a proc sql question

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;

All Replies
Super Contributor
Posts: 320

## Re: Help with a proc sql question

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;

Solution
‎10-30-2013 07:20 PM
Super Contributor
Posts: 1,636

## Re: Help with a proc sql question

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 2 replies
• 196 views
• 3 likes
• 3 in conversation