turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculated a weighted Average Interest Rate

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2016 03:37 AM

Dear Sas Community:

I am trying to calculate a a weighted Average Interest Rate for a company that may have several subsidiaries that borrow at different interest rates.

I have the following table:

CompanyNum | SubsidiaryNum | Amount | Rate |

1 | 23 | 45000000 | 355 |

1 | 24 | 35000000 | 405 |

I want the following table:

CompanyNum | Total Amount | Weighted Average Rate |

1 | 80000000 | 376.875 |

How can I do that using SAS Proc SQL. Any suggestions. Basically I would like to have my analysis at the company level.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Agent1592

12-05-2016 04:21 AM

data watch;

input CompanyNum SubsidiaryNum Amount Rate;

datalines;

1 23 45000000 355

1 24 35000000 405

;

run;

proc sql;

select

CompanyNum,

sum(Amount) as Total_Amount,

sum(Amount*Rate)/sum(Amount) as wRate

from watch

group by CompanyNum;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Agent1592

12-05-2016 06:39 AM - edited 12-05-2016 06:43 AM

Why not use the procedure that SAS intended for this purpose? It would be easy to expand the number of vars, or request other statistics (std, var, min, max, median, various percentiles etc.)

**proc** **summary** data=have noprint nway ;

class companynum;

var rate;

weight amount;

output out=want (drop=_type_ _freq_) sumwgt(rate)=total_amount mean(rate)=wrate ;

**run**;