02-22-2018 03:35 PM
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;
02-22-2018 03:38 PM
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.
02-22-2018 04:38 PM
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.
02-22-2018 05:07 PM
Append is more advantageous because its not expecting any calculations and is a straight 'copy' type task.
Other approaches allow you to modify the data while inserting/updating/union which can also have it's own advantages.
02-22-2018 10:56 PM - edited 02-22-2018 11:01 PM
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.
Need further help from the community? Please ask a new question.