Help using Base SAS procedures

Help with a proc sql question

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

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;

View solution in original post


All Replies
Regular Contributor
Posts: 244

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.

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

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