I have data that I need to convert to a summary with counts for rule (the number or records in each rule by store) and also a % of match by the bucket value.
I am do not know how to convert to summary like the example below. I have tried proc tabulate and proc transpose but have not been able to get the desired results.
any assistance will be greatly appreciated.
Thanks, Elliott
I need it to look like this with counts for rule level and number mismatch and total
the on the right the percent of bucket aging match/mismatch
Want:
Count by matching Rule Level
% of match by aging bucket
Store#
1
2
3
4
5
6
Mis-match
Total
0-5
6-15
16-30
31-60
>60
Mismatch
00070
00144
00145
00163
00175
00251
00301
Data I have looks like this:
Store Rule bktdays match bucket 00004 NOMATCH 0-5 00004 2 3 MATCH 0-5 00011 4 -75 MATCH 0-5 00014 4 -10 MATCH 0-5 00014 1 8 MATCH 6-15 00014 4 -42 MATCH 0-5 00014 4 -45 MATCH 0-5 00014 4 -47 MATCH 0-5 00058 3 0 MATCH 0-5 00070 5 -1 MATCH 0-5 00070 3 0 MATCH 0-5 00070 4 -2 MATCH 0-5 00070 3 0 MATCH 0-5 00070 1 0 MATCH 0-5 00070 4 -9 MATCH 0-5 00070 1 5 MATCH 0-5 00070 4 -14 MATCH 0-5 00070 2 2 MATCH 0-5 00070 4 -18 MATCH 0-5 00070 3 0 MATCH 0-5 00070 4 -21 MATCH 0-5 00070 1 0 MATCH 0-5 00070 4 -25 MATCH 0-5 00070 4 -25 MATCH 0-5 00070 3 3 MATCH 0-5 00070 1 1 MATCH 0-5 00070 4 -28 MATCH 0-5 00070 4 -30 MATCH 0-5 00070 3 2 MATCH 0-5 00070 4 0 MATCH 0-5 00070 4 -42 MATCH 0-5 00070 4 -48 MATCH 0-5 00070 1 0 MATCH 0-5
data have; infile datalines dlm=',' dsd; length Store $5 Rule 1. bktdays8. match $10 bucket $5; input store match bucket $ rule 1. bktdays 8.; datalines; 00004,,,,NOMATCH,0-5 00004,,2,3,MATCH,0-5 00011,,4,-75,MATCH,0-5 00014,,4,-10,MATCH,0-5 00014,,1,8,MATCH,6-15 00014,,4,82,MATCH,61-90 00014,,4,45,MATCH,31-60 00014,4,47,MATCH,31-60 00058,3,0,MATCH,0-5 00070,5,-1,MATCH,0-5 00070,3,0,MATCH,0-5 00070,4,-2,MATCH,0-5 00070,3,0,MATCH,0-5 00070,1,0,MATCH,0-5 00070,4,-9,MATCH,0-5 00070,1,5,MATCH,0-5 00070,,-14,NOMATCH,0-5 00070,2,2,MATCH,0-5 00070,4,-18,MATCH,0-5 00072,3,0,MATCH,0-5 00072,4,21,MATCH,16-30 00073,1,0,MATCH,0-5 00073,4,25,MATCH,16-30 00073,4,25,MATCH,16-30 00074,3,3,MATCH,0-5 00074,1,1,MATCH,0-5 00074,4,58,MATCH,31-60 00075,4,30,MATCH,16-30 00075,3,2,MATCH,0-5 00075,4,0,MATCH,0-5 00076,4,92,MATCH,>90 00076,4,48,MATCH,31-60 00076,1,0,MATCH,0-5 00077,4,52,MATCH,31-60 ; run;
... View more