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...
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: