Hi
I have written this code to create a summary with number of apps approved in each category and total number of apps in the respective
category so that I can run pivot table to create %age of each type of app, no of apps approved by each type/ total no of apps for each type * 100
my question is , is there any better way of writing this proc sql code: please advise.
proc sql;
create table tk.summary_category2_level as
select
app_type_flag , ast, bureau , collatoral, affordability, fraud , general , internalconduct , purpose ,
borrower, forcerefer, expense,
count(*) as num_apps
,sum(case when final_decision = 'Approved' and ast = 1 then 1 else 0 end) as AST_approved
,sum(case when final_decision = 'Approved' and bureau =1 then 1 else 0 end) as BUREAU_approved
,sum(case when final_decision = 'Approved' and collatoral =1 then 1 else 0 end) as COLLATORAL_approved
,sum(case when final_decision = 'Approved' and affordability =1 then 1 else 0 end) as AFFORDABILITY_approved
,sum(case when final_decision = 'Approved' and fraud =1 then 1 else 0 end) as FRAUD_approved
,sum(case when final_decision = 'Approved' and general =1 then 1 else 0 end) as GENERAL_approved
,sum(case when final_decision = 'Approved' and internalconduct =1 then 1 else 0 end) as INTERNALCONDUCT_approved
,sum(case when final_decision = 'Approved' and purpose =1 then 1 else 0 end) as PURPOSE_approved
,sum(case when final_decision = 'Approved' and borrower =1 then 1 else 0 end) as BORROWER_approved
,sum(case when final_decision = 'Approved' and forcerefer =1 then 1 else 0 end) as FORCEREFER_approved
,sum(case when final_decision = 'Approved' and expense =1 then 1 else 0 end) as EXPENSE_approved
,sum(ast) as Total_AST
,sum(bureau) as Total_BUREAU
, sum(collatoral) as Total_COLLATORAL
, sum(affordability) as Total_AFFORDABILITY
, sum(fraud) as Total_FRAUD
, sum(general) as Total_GENERAL
, sum(internalconduct) as Total_INTERNALCONDUCT
, sum(purpose) as Total_PURPOSE
, sum(borrower) as Total_BORROWER
, sum(forcerefer) as Total_FORCEREFER
, sum(expense) as Total_EXPENSE
from
tk.SBOS_final
group by
app_type_flag , ast, bureau , collatoral, affordability, fraud, general, internalconduct, purpose,
borrower, forcerefer, expense
;
quit;
Well as long as it gives you the correct answers (which I can't verify with the info you have provided), and runs in an acceptable time frame (ditto) and is reasonably understandable (it looks understandable to me) then I'd say it's good to go. If it ain't broke then there is no need to fix it...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.