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!
My suggestion would be to calculate the values ahead of time and use PROC REPORT to display the numbers only. Easier that way, IMO.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.