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;
Partially a misunderstanding of the interaction between "formatted" and the values you have specifically chosen. In FORMATTED order "Other" comes before "Rod" and "Bow" comes before "Other" because that is the sort order of the text values.
Your Sort doesn't use the formatted value of Gearcat, it uses the numeric value, so the formatted values have nothing to do with the Sort order displayed by Proc Print.
You may want to consider looking at this with another procedure that allows more Order options:
proc tabulate data=test; class gearcat /order=formatted; format gearcat gr.; class cust; table gearcat, cust ; run; proc tabulate data=test; class gearcat /order=data; format gearcat gr.; class cust; table gearcat, cust ; run; proc tabulate data=test; class gearcat /order=unformatted; format gearcat gr.; class cust; table gearcat, cust ; run;
And for more fun, try changing your sort order around and then using this
proc tabulate data=test; class gearcat /preloadfmt; format gearcat gr.; class cust; table gearcat, cust /printmiss ; run;
In fact, you might try several sort orders with the previous.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.