Proc Summary and Proc Report

[Kojimasan]

I have a dataset like this:

Account ID	CURRENT BALANCE	Custom Score	Ever60 Flag	Refreshed_FICO_Score	Month	Credit Limit
1	7630.84	687	1	699	Dec-15	12500
1	7665.51	678	1	699	Jan-16	12500
1	8248.72	689	1	699	Feb-16	12500
1	8521.41	666	1	699	Mar-16	12500
1	8900.77	666	1	699	Apr-16	12500
1	9190.59	662	1	699	May-16	12500
1	12167.4	662	1	709	Jun-16	12500
1	12780.75	665	1	709	Jul-16	12500
1	12873.1	643	1	709	Aug-16	12500
1	12689.51	597	1	703	Sep-16	12500
1	12674.99	599	1	703	Oct-16	12500
1	12725.45	592	1	703	Nov-16	12500
1	12811.93	268	1	583	Dec-16	12500
2	1968.19	740	0	811	Dec-15	16000
2	2685.1	768	0	811	Jan-16	16000
2	2742.79	755	0	811	Feb-16	16000
2	23.7	770	0	802	Mar-16	16000
2	163.98	757	0	802	Apr-16	16000
2	308.21	768	0	802	May-16	16000
2	1004.38	756	0	846	Jun-16	16000
2	1332.81	751	0	846	Jul-16	16000
2	1733.46	761	0	846	Aug-16	16000
2	2395.12	761	0	834	Sep-16	16000
2	1742.65	761	0	834	Oct-16	16000
2	2137.87	766	0	834	Nov-16	16000
2	1210.18	760	0	815	Dec-16	16000
3	168.59	711	0	790	Dec-15	1000
3	59.86	743	0	790	Jan-16	1000
3	77.1	688	0	790	Feb-16	1000
3	88.83	727	0	800	Mar-16	1000
3	0	727	0	800	Apr-16	1000
3	0	727	0	800	May-16	1000
3	0	727	0	800	Jun-16	1000
3	0	727	0	800	Jul-16	1000
3	0	727	0	800	Aug-16	1000
3	0	727	0	800	Sep-16	1000
3	0	727	0	800	Oct-16	1000
3	0	727	0	800	Nov-16	1000
3	0	727	0	800	Dec-16	1000

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
	0	400-660	661-680	681-700	701-720	721-740	741-760	761-780	781-800	801-820	821-840	841-860	9001	9002	9003
Custom Score	Statistic
400-660	% Bad Rate
	N Bad Accounts	0	280	39	31	18	9	5	2	1	0	0	0	1	0	5
	Total Accounts	4	1,423	351	345	276	175	98	56	31	31	17	10	5	1	13
661-680	% Bad Rate
	N Bad Accounts	.	183	58	61	41	20	10	3	0	0	0	0	3	0	0
	Total Accounts	.	2,187	995	1,167	1,058	806	453	215	101	65	23	3	19	5	9
681-700	% Bad Rate
	N Bad Accounts	1	240	80	87	76	44	29	11	6	2	1	1	0	2	3
	Total Accounts	7	3,061	1,840	2,412	2,673	2,405	1,575	1,015	571	338	147	44	34	9	34

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!

[Reeza]

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