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 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;
so go back to unique loan level data and pull from there ? 🙂
Calling @Rick_SAS
@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?
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.