The code below works fine, the first part groups by date,fico,product, the second part does not group on fico, as I want it to be on the portfolio level, hence the variable 'Portfolio' as fico format=$9.
Is there a way to do this in one step without the union? I'd like to only have one 'from' statement. Pulling from the dataset twice doubles the time it takes to build this and I have many of them.
create table avg_fico as
select distinct intnx('month',OPEN_DT,0,'e') as OPEN_DT format=mmddyy10.,fico as fico,product,sum(cbscore_new) as cbscore_num,sum(n_cbscore_new) as cbscore_denom
group by OPEN_DT,fico,product
select distinct intnx('month',OPEN_DT,0,'e') as OPEN_DT format=mmddyy10.,'Portfolio' as fico format=$9.,product,sum(cbscore_new) as cbscore_num,sum(n_cbscore_new) as cbscore_denom
group by OPEN_DT,product
order by OPEN_DT,fico,product;
PROC MEANS will do multiple levels of calculation at once - since you're using different GROUP BY I'm not sure there's an easy way in SQL to accomplish that. It will also respect variable formats so you can apply a format to the date to ensure the calculation is correct.
Not sure if this is a new topic or not but would you recommend this over doing an insert into, append or another data step with a set statement? I have seen most sql developers use the insert into method over the union but due to how SAS compiles am not sure which method is most advantageous.
Did you try using a PROC instead?
proc summary data=mdj.uloc_format ; class open_dt product fico ; types open_dt*product*fico open_dt*product ; format open_dt yymm7. ; var cbscore_new n_csscore_new ; output out=avg_fico sum=cbscore_num cbscore_denum ; run;
If you have to roll you own then summarize at the most detailed level first and then build the higher level summaries from the summary instead of query the raw data again.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.