I have followed Rick Wicklin's post about how to get Proc Freq to create tables in the order you want. However, even though the data get sorted correctly, the output table is not displayed/sorted correctly. Notice how when I printed the sorted dataset Cust "C" has all three gear types sorted correctly (Rod, Bow, Other); however, the output table using that variable (gearcat) and order=formatted, does not (instead output is alphabet).
My guess is this is because the first table value (Cust A) does not have all combinations of the second table variable (gearcat). I also tried using the "sparse" option in hopes that might help, but nothing changed.
My question: how can I get Proc Freq to use the sorted order of the second variable if/when the first entry of the first variable doesn't contain a full compliment of the second variable? I tried a hack where I gave every customer every gear, but with zero weights for those not in the "real" data, but that didn't help.
/* Listing of Customers and Gears */
data test;
input Cust $ Gear $ ;
datalines;
A Bow
B Rod
C Bow
C Rod
C Other
;
run;
/* Create format that links Gear Categories in order to their labels */
proc format;
value gr
1='Rod'
2='Bow'
3='Other'
;
run;
/* Create numeric GearCat for sorting */
data test;
format gearcat gr.;
set test;
select (Gear);
when ('Rod') gearcat=1;
when ('Bow') gearcat=2;
when ('Other') gearcat=3;
when (' ') gearcat=.;
end;
/* Sort by Customer, then gearcat */
proc sort data=test; by cust gearcat;run;
proc print data=test;run; /* Shows the internal order is correct, see Customer C */
/* Create a 2-way table using order=formatted*/
/* Note how the order is wrong, even though the values were sorted properly */
/* Gearcat should be Rod - Bow - Other, not alphabetic */
proc freq data=test order=formatted;
tables cust*gearcat/nopercent nocol norow;
run;
... View more
