## Calculating Ratio

Solved
Regular Contributor
Posts: 194

# Calculating Ratio

Hi All,

I attach a file which is a part of my dataset. I need to calculate the following ratio:

where i is the insiderID and t is the year. So the ratio will have to be calculated every year. You will find the buy and sell under the variable transactionType. You need to use the variable "shares" which is the number of shares bought and sold.

Thanks.

Accepted Solutions
Solution
‎10-19-2014 02:59 PM
Posts: 1,270

## Re: Calculating Ratio

This message tells observations not accounted by case statement will have a missing value under ratio variable. I've used coalesce function that will replace missing values with 0. We can add else with 0 in case statement like this.

proc sql;

create table want as

select companyisin,insiderID,year,sum(case when transactionType='Buy' then shares else 0 end)/sum(shares) as Ratio

from insider_sample group by companyisin,insiderID,year;

quit;

All Replies
Super Contributor
Posts: 490

## Re: Calculating Ratio

Check this:

proc sort  data=work.insider_sample0874  (keep=companyisin insiderID year transactionType shares);

by companyisin insiderid year transactionType;

run;

data total;

set work.insider_sample0874;

by insiderID year transactionType;

if First.transactionType then tot = 0;

tot + shares;

if last.transactionType then output;

run;

data ratio(keep=companyisin insiderID year ratio);

set total;

by insiderID year;

retain x;

if last.year and first.year and transactionType ="Sell" then do ;

ratio= 0;

output;

end;

Else if last.year and last.year and transactionType ="Buy" then do;

ratio=1 ;

output;

end;

Else do ;

if transactionType ="Buy" then x = tot;

else do;

ratio= x/(x+shares);

x = 0;

output;

end;

end;

run;

Regular Contributor
Posts: 194

## Re: Calculating Ratio

Hi,

It shows the following error messages:

ERROR: BY variables are not properly sorted on data set WORK.INSIDER_SAMPLE0874.

NOTE: The SAS System stopped processing this step because of errors.

So what can be done to correct it? However, I wrote the following code and please check whether it's ok. Also, let me know whether there is a better way.

set Insider_sample;

else if transactionType = 'Sell' then Buy = 0;

if transactionType = 'Sell' then Sell = shares;

else if transactionType = 'Buy' then Sell = 0;

run;

proc sql;

create table insider_yearly as

select distinct

year,

companyISIN,

insiderID,

sum(Sell) as yearly_sell,

group by

insiderID,

year;

quit;

data insider_ipr;

set insider_yearly;

run;

Super Contributor
Posts: 490

## Re: Calculating Ratio

change WORK.INSIDER_SAMPLE0874. by the file name in your machine.

this was the name when i imported your data set on my machine

Regular Contributor
Posts: 194

## Re: Calculating Ratio

I changed the file name. Yor first datastep (proc sort data) works. If I include the CompanyISIN after "by" in the second datastep (data total), then it works. Your third datastep is not working. It shows the following error message.

ERROR: BY variables are not properly sorted on data set WORK.TOTAL

So what can be done to correct?

By the by, what about my code?

Regular Contributor
Posts: 194

## Re: Calculating Ratio

No, still it shows that error message when I run your third datastep.

Super Contributor
Posts: 490

## Re: Calculating Ratio

make sure to run the three procs

the error you mentioned indicate that the data is not sorted

so make sure to run the first PROC SORT

Regular Contributor
Posts: 194

## Re: Calculating Ratio

I ran all the datasteps one by one mentioned by you. First and second datasteps are ok. But when I am running the third datastep then it shows that error message.

Regular Contributor
Posts: 194

## Re: Calculating Ratio

It's working when I am using the dataset that I attached here before. That was a part of my dataset. In this dataset there is only one insider trader. When I am working with the complete dataset, then it shows that error message.

Super Contributor
Posts: 490

## Re: Calculating Ratio

If this is the case then it should be like the following:

data ratio(keep=companyisin insiderID year ratio);

set total;

by  companyisin insiderID year transactionType;

retain x;

if last.year and first.year and transactionType ="Sell" then do ;

ratio= 0;

output;

end;

Else if last.year and last.year and transactionType ="Buy" then do;

ratio=1 ;

output;

end;

Else do ;

if transactionType ="Buy" then x = tot;

else do;

ratio= x/(x+shares);

x = 0;

output;

end;

end;

run;

Any way your code is working too.

Regular Contributor
Posts: 194

## Re: Calculating Ratio

It's working now. Thanks a lot.

Posts: 1,270

## Re: Calculating Ratio

proc sql;

create table want as

select companyisin,insiderID,year,coalesce(sum(case when transactionType='Buy' then shares end)/sum(shares),0) as Ratio

from insider_sample group by companyisin,insiderID,year;

quit;

Regular Contributor
Posts: 194

## Re: Calculating Ratio

It's working but shows the following message:

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will

result in a missing value for the CASE expression.

What can I do now?

Solution
‎10-19-2014 02:59 PM
Posts: 1,270

## Re: Calculating Ratio

This message tells observations not accounted by case statement will have a missing value under ratio variable. I've used coalesce function that will replace missing values with 0. We can add else with 0 in case statement like this.

proc sql;

create table want as

select companyisin,insiderID,year,sum(case when transactionType='Buy' then shares else 0 end)/sum(shares) as Ratio

from insider_sample group by companyisin,insiderID,year;

quit;

Regular Contributor
Posts: 194

## Re: Calculating Ratio

So, this means I can use both codes, both the previous code and latest code. Right?

🔒 This topic is solved and locked.