@Elliott wrote:
Thank you this is wonderful, as for the last column it is supposed to be the % of mismatches. I need to add that.
Then why is it included as a subheading under the group heading titled "% of match by aging bucket"?
If that is just a layout error, then in the DATA NEED step, make a new dummy variable "mismatch_dummy", with a one for mismatch, and zero otherwise. In proc tabulate, add the mismatch_dummy as an analysis variable, and ask for its mean with a PERCENT display.
Also, how would the code change if they actually wanted a count on buckets instead of %match?
Since the %match values are nothing more than the mean of a bunch of 1's (match) and 0's for the analysis variable (not class variable) match_dummy (which I called match_pct in my earlier note), then to get a "count" of matches instead, replace the code requesting a mean to a request for a sum. The sum of all those 1's will be the same as a count of those 1's. Don't forget to change the associated format request.
This is a common "trick" to use with proc tabulate. If match_dummy had been declared a class variable - which might have been the intuitive instinct, then proc tabulate would generate two columns (or rows) for it - one for the 0's and one for the 1's. But you want only one column. Using match_dummy as an analysis variable, and coding it only as 1's and 0's allows you to replicate a count (which is the normal use of a class variable) with a sum.
Of course, the same was done with the mismatch_dummy variable, except you want a mean instead of a sum.
To make the code clearer, take away all the quoted elements, and maybe the formats - then you'll see the operational code.
data work.summary_data;
infile datalines dlm=',' dsd truncover;
length PendingNo $9 CrUnit $5 Store $5 match $10 bucket $5 Rulechar $1 Rule 3 ;
input PendingNo CrUnit Store match bucket Rulechar $ Rule 1. ;
datalines;
253864481,4730,4,MATCH,0-5,2,2
212777089,4730,4,NOMATCH,0-5,,
301172842,4730,11,MATCH,0-5,4,4
281739414,4730,14,MATCH,6-15,1,1
261300392,4730,14,MATCH,0-5,4,4
288475892,4730,14,MATCH,0-5,4,4
313076925,4730,14,MATCH,0-5,4,4
23131926,4730,14,MATCH,0-5,4,4
273107812,4730,58,MATCH,0-5,3,3
226270010,4730,70,MATCH,0-5,1,1
276177164,4730,70,MATCH,0-5,1,1
92644582,4730,70,MATCH,0-5,1,1
137788903,4730,70,MATCH,0-5,1,1
63797089,4730,70,MATCH,0-5,1,1
114514092,4730,70,MATCH,6-15,1,1
159810023,4730,70,MATCH,0-5,1,1
239028597,4730,70,MATCH,6-15,1,1
291638142,4730,70,MATCH,0-5,1,1
306576926,4730,70,MATCH,0-5,2,2
184156646,4730,70,MATCH,0-5,3,3
214132768,4730,70,MATCH,0-5,3,3
51683419,4730,70,MATCH,0-5,3,3
137563134,4730,70,MATCH,0-5,3,3
217461698,4730,70,MATCH,0-5,3,3
109113011,4730,70,MATCH,0-5,3,3
149173652,4730,70,MATCH,0-5,3,3
154079135,4730,70,MATCH,0-5,3,3
189377914,4730,70,MATCH,0-5,3,3
204766847,4730,70,MATCH,0-5,3,3
189066497,4730,70,MATCH,0-5,4,4
251583641,4730,70,MATCH,0-5,4,4
306428966,4730,70,MATCH,0-5,4,4
46074769,4730,70,MATCH,0-5,4,4
72116021,4730,70,MATCH,0-5,4,4
112755683,4730,70,MATCH,0-5,4,4
112963673,4730,70,MATCH,0-5,4,4
147893714,4730,70,MATCH,0-5,4,4
162142576,4730,70,MATCH,0-5,4,4
233991350,4730,70,MATCH,0-5,4,4
283368603,4730,70,MATCH,0-5,4,4
38519097,4730,70,MATCH,0-5,4,4
78059359,4730,70,MATCH,0-5,4,4
104160371,4730,130,MATCH,0-5,4,4
119392631,4730,130,MATCH,0-5,4,4
184920636,4730,130,MATCH,0-5,4,4
81757207,4730,130,MATCH,0-5,4,4
96275766,4730,130,MATCH,0-5,4,4
96633756,4730,130,MATCH,0-5,4,4
214350928,4730,139,MATCH,0-5,1,1
13148566,4730,139,MATCH,0-5,3,3
164847505,4730,139,MATCH,0-5,4,4
184356706,4730,139,MATCH,0-5,4,4
67873620,4730,139,MATCH,0-5,4,4
72534031,4730,139,MATCH,0-5,4,4
102649143,4730,139,MATCH,0-5,4,4
182921667,4730,139,MATCH,0-5,4,4
207836158,4730,139,MATCH,0-5,4,4
238375000,4730,139,MATCH,0-5,4,4
278454232,4730,139,MATCH,0-5,4,4
298747613,4730,139,MATCH,0-5,4,4
251074531,4730,139,NOMATCH,0-5,,
293309254,4730,139,NOMATCH,0-5,,
204006727,4730,139,NOMATCH,0-5,,
106813447,4730,139,NOMATCH,0-5,,
43030128,4730,140,MATCH,6-15,1,1
291264232,4730,140,MATCH,0-5,1,1
296210900,4730,140,MATCH,0-5,2,2
99259380,4730,140,MATCH,16-30,3,3
51328329,4730,143,MATCH,16-30,1,1
run;
data need / view=need;
set work.summary_data ;
/*Put a leading space in front of '0-5' and '6-15' so that
lexicographic order replicates the intended age group order*/
if bucket='0-5' then bucket=' 0-5';
else if bucket='6-15' then bucket=' 6-15';
if rule=. then do;
rule=99 ; /*Use for mismatch*/
match_dummy=0;
end;
else match_dummy=1;
mismatch_dummy=1-match_dummy;
/*Right justify STORE variable to get expected sort order*/
store=right(store);
run ;
proc format ;
value rulefmt 99='MisMatch' other=[1.] ;
run;
proc tabulate data =need;
class rule store bucket ;
var match_dummy mismatch_dummy;
table store
, rule*n=' '*f=comma6.0
all='Total'*N=''*f=comma6.0
match_dummy='% of Match by Aging Bucket'*bucket=' '*sum=' '*f=comma6.0
mismatch_dummy='MisMatch %'*mean=' '*f=percent5.;
format rule rulefmt. ;
run;
... View more
