Proc freq below doesn't give me output ordered on the Freq. Do you know why? and how to fix it?
proc freq data=have order=freq;
tables a1*a2*a3*a4*a5*total/list missing nocum;
run;
That solution is not the same as your original question...If you want counts, proc means is a good option as well.
Note that the LABEL should work, and if it's not I suspect there's some error in your code before this. You can include the log if you have further errors.
Here's an approach with PROC MEANS.
*generate fake data;
data have;
array a(17);
do i=1 to 100;
do j=1 to 17;
a(j)=rand('bernoulli', 0.27);
if rand('bernoulli', 0.1)=1 then
call missing(a(j));
end;
output;
end;
run;
*Summary statistics;
proc means data=have stackods n sum mean maxdec=1;
var a1-a17;
ods output summary=want;
run;
*sort by N, descending count, and print;
proc sort data=want;
by descending n;
run;
proc print data=want label noobs;
label variable='Defect Categories';
run;
I think it orders within the groups.
So if you pick A*B, it orders within the A*B groups descending, which isn't the behaviour you want. I think the easiest is to sort it afterwards yourself or use SQL instead.
Hi Reeza,
My a1-a17 is dummy variables. Do you have any suggestions on how to feed "order by" in sql below?
proc sql;
create table freq as
select count(a1) as a1, count(a2) as a2, count(a3) as a3, count(a4) as a4,
count(a5) as a5,count(a6) as a6,count(a7) as a7, count(a8) as a8, count(a9) as a9,
count(a10) as a10,count(a11) as a11,count(a12) as a12, count(a13) as a13, count(a14) as a14,
count(a15) as a15,count(a16) as a16,count(a17) as a17
from op_only2;
order by...?
quit;
That doesn't seem like the equivalent query to me. I'm not 100% clear on what you're trying to achieve anymore.
I strongly recommend sticking with PROC FREQ and a sort if you're not familiar with SQL. Mostly because FREQ will support variable lists, PROC SQL does not.
Proc freq data=have NOPRINT;
Table .... / out = want;
Run;
Proc sort data=want;
By descending count;
Run;
Hi Reeza,
Sorry for ambiguity. Below approach worked out except PROC PRINT ignores my attempt to label the variable. I'd like to use proc print output as PDF. Should I make this Proc Print label issue separate post? There was another SOLVED post on this topic which suggested to use 'label' statement in the proc print. That didn't work out for me.
proc sql;
create table freq as
select count(a1) as a1, count(a2) as a2, count(a3) as a3, count(a4) as a4,
count(a5) as a5,count(a6) as a6,count(a7) as a7, count(a8) as a8, count(a9) as a9,
count(a10) as a10,count(a11) as a11,count(a12) as a12, count(a13) as a13, count(a14) as a14,
count(a15) as a15,count(a16) as a16,count(a17) as a17
from op_only2;
quit;
proc transpose data=freq out=freq1; run;
proc sort data=freq1;
by descending col1;
run;
proc print data=freq1 NOOBS LABEL;
format _name_ $group.;
label _name_="DEFECT CATEGORIES";
run;
That solution is not the same as your original question...If you want counts, proc means is a good option as well.
Note that the LABEL should work, and if it's not I suspect there's some error in your code before this. You can include the log if you have further errors.
Here's an approach with PROC MEANS.
*generate fake data;
data have;
array a(17);
do i=1 to 100;
do j=1 to 17;
a(j)=rand('bernoulli', 0.27);
if rand('bernoulli', 0.1)=1 then
call missing(a(j));
end;
output;
end;
run;
*Summary statistics;
proc means data=have stackods n sum mean maxdec=1;
var a1-a17;
ods output summary=want;
run;
*sort by N, descending count, and print;
proc sort data=want;
by descending n;
run;
proc print data=want label noobs;
label variable='Defect Categories';
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.