BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SeanZ
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

2 REPLIES 2
snoopy369
Barite | Level 11

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
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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