Proc Summary and Proc Report

Reply
Occasional Contributor
Posts: 7

Proc Summary and Proc Report

[ Edited ]

I have a dataset like this:

 

Account IDCURRENT BALANCECustom ScoreEver60 FlagRefreshed_FICO_ScoreMonthCredit Limit
17630.846871699Dec-1512500
17665.516781699Jan-1612500
18248.726891699Feb-1612500
18521.416661699Mar-1612500
18900.776661699Apr-1612500
19190.596621699May-1612500
112167.46621709Jun-1612500
112780.756651709Jul-1612500
112873.16431709Aug-1612500
112689.515971703Sep-1612500
112674.995991703Oct-1612500
112725.455921703Nov-1612500
112811.932681583Dec-1612500
21968.197400811Dec-1516000
22685.17680811Jan-1616000
22742.797550811Feb-1616000
223.77700802Mar-1616000
2163.987570802Apr-1616000
2308.217680802May-1616000
21004.387560846Jun-1616000
21332.817510846Jul-1616000
21733.467610846Aug-1616000
22395.127610834Sep-1616000
21742.657610834Oct-1616000
22137.877660834Nov-1616000
21210.187600815Dec-1616000
3168.597110790Dec-151000
359.867430790Jan-161000
377.16880790Feb-161000
388.837270800Mar-161000
307270800Apr-161000
307270800May-161000
307270800Jun-161000
307270800Jul-161000
307270800Aug-161000
307270800Sep-161000
307270800Oct-161000
307270800Nov-161000
307270800Dec-161000

 

 

 

 

 

I'm achieving the initial results below the code as follows:

 

proc summary data=abe.triadtabulate_all nway;
	class t0001 original_fico_score;
	var eversixtyflag1;
	format t0001 triad_groups. original_fico_score fico_groups.;
	output out=_stats_ sum=sum mean=percent n=n;
run;

/* Re-arrange data so that PROC REPORT will have proper row labels in the variable named statistic */
data _stats2_;
	set _stats_;
	length statistic $ 16;
	value=percent;
	statistic='% Bad Rate';
	output;
	value=sum;
	statistic='N Bad Accounts';
	output;
	value=n;
	statistic='Total Accounts';
	output;
	keep t0001 original_fico_score _type_ _freq_ value statistic;
run;

/* Count the number of distinct FICO columns */
proc sql noprint;
	select count(distinct original_fico_score) into :nficos from _stats2_;
quit;

/* Create formatting for each of the three rows in each of the FICO columns */
%macro formatting(nficos);
	%do ii=3 %to %eval(&nficos+2);
		if statistic='% Bad Rate' then call define (&ii,'format','percent7.1');
		else if statistic='N Bad Accounts' then call define (&ii,'format','comma7.0');
		else if statistic='Total Accounts' then call define (&ii,'format','comma8.0');
	%end;
%mend;
 
/* Create PROC REPORT output, with desired formatting, and thicker lines between the TRIAD groups */
ods html;
proc report data=_stats2_;
	columns t0001 statistic original_fico_score,value;
	define t0001/format=triad_groups. group "Triad Score" order=internal;
	define statistic/group "Statistic";
	define original_fico_score/across order=internal "FICO" format=fico_groups.;
	define value/analysis sum " ";
	compute original_fico_score;
		%formatting(&nficos)
	endcompute;
	compute statistic;
		if statistic='% Bad Rate' then call define(_row_,'style','style={bordertopwidth=3}');
	endcompute;
run;
ods html close;

 

 

 

These are my initial results with just counting bad rate, n bad accounts, and n total accounts. 

 

 REFRESHED FICO
 0400-660661-680681-700701-720721-740741-760761-780781-800801-820821-840841-860900190029003
Custom ScoreStatistic 
400-660% Bad Rate
 N Bad Accounts02803931189521000105
 Total Accounts41,4233513452761759856313117105113
661-680% Bad Rate
 N Bad Accounts.183586141201030000300
 Total Accounts.2,1879951,1671,058806453215101652331959
681-700% Bad Rate
 N Bad Accounts12408087764429116211023
 Total Accounts73,0611,8402,4122,6732,4051,5751,0155713381474434934

 

 

But now that I want, the proc report to look like this:

Bad % = ( Bad accounts/total accounts)

N BAD ACCOUNTS = (Sum of bad accounts)

Total Accounts = (Bad + good accounts)

Bad Balance % = (Bad Balances/ Total Balance)

Bad Balance = (Sum of Bad Balance)

Total Balance ( Bad + Good balance)

Bad Credit % = (Bad Credit Lines / Total Credit Lines)

Bad Credit Line = (Sum of Bad Credit Lines)

Total Credit Line = (bad + good credit lines)

 

How do i go about this adding variables to the report and define values?

I'm so confused!

 

 

 

 

 

Super User
Posts: 23,752

Re: Proc Summary and Proc Report

Posted in reply to Kojimasan

My suggestion would be to calculate the values ahead of time and use PROC REPORT to display the numbers only. Easier that way, IMO.

Ask a Question
Discussion stats
  • 1 reply
  • 96 views
  • 0 likes
  • 2 in conversation