topic Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages in SAS Programming

Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

novinosrin — Mon, 11 Jan 2021 22:32:16 GMT

Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

Hi, My I have A.) detailed normalized banking data with unique loan info for each loan. Also, B.)I have a portfolio level aka loan categorized into groups. Both are monthly snapshots.

The portfolio level has monthly weighted averages that were consolidated using loan level data. To get some quarterly reporting, I tried to weight the weighted of monthly portfolio data with the anticipation those will align with results of the unique loan level data weighted by quarterly.

However the results seem pretty different. Not sure if me being tired has anything to do with it. Am i being silly in missing something that's obvious?

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

Reeza — Mon, 11 Jan 2021 22:34:09 GMT

I would assume if the weighted data is just summed it should reflect the quarterly values? No need to reweight? Or is the quarterly using different weights that reflect the three months totals?

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

novinosrin — Mon, 11 Jan 2021 22:38:03 GMT

@Reeza Thank you for speedy response. Below is querying the monthly portfolio consolidated aka group by portfolio, monthly. Does this give a better idea?

The source total orig data is monthly portfolio consolidated file

/*wa_fico wa_cltv wa_rate*/

%let prime=0.0325;
%let QTR=2020Q4;
data heloc;
 set ccrm_dnt.total_origination
 (keep=wa_fico wa_cltv wa_rate units dollars portfolio date);
 where portfolio in ('HELOC Jr Lien','HELOC Sr Lien')
 and put(intnx('qtr',date,0,'e'),yyq6. -l)="&QTR";
 retain prime &prime;
 Monthly_spread=wa_rate-prime;
run;

proc sql;
 create table heloc_summary as
 select portfolio,
 intnx('qtr',date,0,'e') as qtr format=yyq6. ,
 sum(wa_fico * dollars) / sum(dollars) as wa_fico format=8. label='WA FICO',
 sum(wa_cltv * dollars) / sum(dollars) as wa_cltv format=8. label='WA LTV',
 sum(Monthly_spread *dollars)/sum(dollars) as Spread,
 sum(dollars)/sum(units) as average_limit format=dollar20.2,
 sum(units) as count
 from heloc
 group by portfolio,qtr;
quit;

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

Reeza — Mon, 11 Jan 2021 22:39:49 GMT

Yes, unfortunately I don't think there's a way to simplify those weights, at least my basic algebra is saying the math wouldn't really be worth the effort. I think you'd need the same calculations essentially to figure out the new weights unless they happened to be constant (which is unlikely).

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

novinosrin — Mon, 11 Jan 2021 22:40:58 GMT

so go back to unique loan level data and pull from there ? 🙂

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

Reeza — Mon, 11 Jan 2021 22:43:10 GMT

Yes, or if you happen to have an intermediary table that just has the sums of each quantity per month that may work as well. We create a set of monthly summaries of common stats such as these, usually just count and totals, for that reason.

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

Ksharp — Tue, 12 Jan 2021 12:09:39 GMT

Calling @Rick_SAS

Re: Requesting rather a perhaps simple math help in SAS plz?Weighted averages of weighted averages

novinosrin — Wed, 20 Jan 2021 16:10:03 GMT

@Reeza Yes monthly Portfolio summary file indeed exists and that seem to have worked. However, are we in sync that always better to go back to loan level data to play with loan characteristics at source and not at summary? Although, product of summed_outstanding* weighted average is expected to give the sum(outstanding*fico) etc. Final thoughts?