Desktop productivity for business analysts and programmers

proc sql summary code:

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

proc sql summary code:

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;

 


Accepted Solutions
Solution
‎12-31-2017 06:25 AM
Super User
Posts: 5,917

Re: proc sql summary code:

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.
Data never sleeps

View solution in original post


All Replies
Super User
Posts: 4,023

Re: proc sql summary code:

[ Edited ]

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

Solution
‎12-31-2017 06:25 AM
Super User
Posts: 5,917

Re: proc sql summary code:

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.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 211 views
  • 2 likes
  • 3 in conversation