Proc Summary and Proc Report

Kojimasan — Tue, 03 Apr 2018 21:00:19 GMT

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!

Re: Proc Summary and Proc Report

Reeza — Tue, 03 Apr 2018 21:08:06 GMT

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

topic Proc Summary and Proc Report in SAS Programming

Proc Summary and Proc Report

Re: Proc Summary and Proc Report