proc sql summary code:

bondtk — Tue, 26 Dec 2017 06:26:08 GMT

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;

Re: proc sql summary code:

SASKiwi — Tue, 26 Dec 2017 08:27:06 GMT

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...

Re: proc sql summary code:

LinusH — Tue, 26 Dec 2017 14:52:11 GMT

Given the amount of hard coded criterias I would say it may require some maintenance over time if your categorizations change.
Pivoting is usually a matter of report layout and can often be handled in the end user interface or in a reporting procedure like TABULATE or REPORT.

topic Re: proc sql summary code: in SAS Enterprise Guide

proc sql summary code:

Re: proc sql summary code:

Re: proc sql summary code: