proc sql summary code:

[bondtk]

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;

[SASKiwi]

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

[LinusH · Accepted solution]

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.