DATA Step, Macro, Functions and more

PROC FREQ doesn't order with order=freq

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 143
Accepted Solution

PROC FREQ doesn't order with order=freq

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;

 


Accepted Solutions
Solution
‎08-28-2017 10:14 AM
Super User
Posts: 19,878

Re: PROC FREQ doesn't order with order=freq

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;

View solution in original post


All Replies
Super User
Posts: 19,878

Re: PROC FREQ doesn't order with order=freq

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. 

Frequent Contributor
Posts: 143

Re: PROC FREQ doesn't order with order=freq

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;
Super User
Posts: 19,878

Re: PROC FREQ doesn't order with order=freq

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;

 

Frequent Contributor
Posts: 143

Re: PROC FREQ doesn't order with order=freq

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;
Solution
‎08-28-2017 10:14 AM
Super User
Posts: 19,878

Re: PROC FREQ doesn't order with order=freq

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;
Frequent Contributor
Posts: 143

Re: PROC FREQ doesn't order with order=freq

Hi Reeza. Thanks millions. Proc means is the way. Btw, I had "options nolabel;" in my program conflicted with any latter label statements, I realized that late and corrected. Now I have any label statements applied with no conflict.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 203 views
  • 1 like
  • 2 in conversation