How to sort by the most common combinations in proc tabulate.

[ZZ_Zheng]

Hi, I have a 6700 person sample size with 9 binary (0 or 1) variables, I want to see the most common (maybe top 20) combinations of these 2^9 possible combinations since most combinations will have 0 or 1 observation. In other words, I want to sort the proc tabulate output by "sum" or "n". I also create a variable called x, which equal 1 for every observation.

Below are my code and SAS output 

proc tabulate data=paper order=freq;
class structural_flag social_support_flag behaviors_flag bills_flag housing_flag mental_health_flag resources_flag jail_flag food_cat;
var x;
table structural_flag*social_support_flag*behaviors_flag*bills_flag*housing_flag*mental_health_flag*resources_flag*jail_flag*food_cat,
	(x)*(Sum colpctn);run;

 

[PGStats · Accepted solution]

How about

proc sql outobs=20;
create table paperTable from
select structural_flag, social_support_flag, behaviors_flag, bills_flag, housing_flag, 
    mental_health_flag, resources_flag, jail_flag, food_cat, count(*) as nn 
from paper
group by structural_flag, social_support_flag, behaviors_flag, bills_flag, housing_flag, 
    mental_health_flag, resources_flag, jail_flag, food_cat
order by nn desc;
quit;

proc tabulate data=paperTable order=data;
class structural_flag social_support_flag behaviors_flag bills_flag housing_flag mental_health_flag 
    resources_flag jail_flag food_cat;
var nn;
table structural_flag*social_support_flag*behaviors_flag*bills_flag*housing_flag*mental_health_flag*
    resources_flag*jail_flag*food_cat,
	nn*(Min colpctsum);
run;

(untested)

[ZZ_Zheng]

Thanks! PG Stats. It works well!