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

[novinosrin]

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? 

[Reeza]

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?

[novinosrin]

@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 ′
 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;

[Reeza]

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).

[novinosrin]

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

[Reeza · Accepted solution]

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.

[Ksharp]

Calling @Rick_SAS 

[novinosrin]

@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?