proc freq order=formatted

[Ronein]

Hello

I am working with proc freq.

I want that in the output , the categories of  Cholesterol  status  will appear in same order as in proc format.

Why the desired result is not coming please?

MY code is:

proc format;
value $Ffmt ( notsorted)
'Desirable'='Desirable'
'Borderline'='Borderline'
'High'='High';
Run;

Data heart;
set sashelp.heart;
Format Chol_Status $Ffmt.;
Run;


proc freq data=heart order=formatted;
table Chol_Status*Status/chisq sparse outpct out=output1;
format Chol_Status $Fm2t.;
run;

[gamotte]

Hello,

This is what the proc freq documentation says about the order=FORMATTED option

https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect00...

FORMATTED

orders values by their formatted values (in ascending order). This order is dependent on the operating environment.

So the alphabetical order of formatted values will be used which is not what you want.

What you want, as i understand it, is the order in which formatted values have been defined in the proc format.

The following program exports format definitions in a SAS dataset and uses this dataset to impose a custom

sort order on the input dataset. Proc freq is then used with the order=data option (i used the program you gave in

your other post on the same subject).

proc format;
    value $Fm1t ( notsorted)
    'Male'='M'
    'Female'='F';

    value $Fm2t ( notsorted)
    'Desirable'='Desirable'
    'Borderline'='Borderline'
    'High'='High';

    value $Fm3t ( notsorted)
    'Underweight'='Underweight'
    'Normal'='Normal weight'
    'Overweight'='Overweight';

    value $Fm4t ( notsorted)
    'Non-smoker'='Non-smoker'
    'Light (1-5)'='Light Smoker (1-5)'
    'Moderate (6-15)'='Moderate Smoker(6-15)'
    'Heavy (16-25)'='Heavy Smoker(16-25)'
    'Very Heavy (> 25)'='Very Heavy Smoker(> 25)';

    value $Fm5t ( notsorted)
    'Optimal'='Optimal Blood Pressure'
    'Normal'='Normal Blood Pressure'
    'High'='High Blood Pressure';

    select $Fm1t $Fm2t $Fm3t $Fm4t $Fm5t;
Run;

Data heart;
    set sashelp.heart;
    Format sex $Fm1t.
    Chol_Status $Fm2t.
    Weight_Status $Fm3t.
    Smoking_Status $Fm4t.
    BP_Status $Fm5t.;
Run;

%macro mmacro1(pred,i);

    proc format library=work.formats fmtlib out=desc;
        select $Fm&i.t;
    run;

    data sort_ds;
        set desc;
        value=start;
        n=_N_;
        keep value n;
    run;

    proc sql;
        CREATE TABLE heart_s AS
        SELECT a.*
        FROM heart a
        LEFT JOIN sort_ds b
        ON b.value=a.&pred.
        ORDER BY b.n;
    quit;

    proc freq data=heart_s order=data;  
        tables &pred*Status/chisq sparse outpct out=outfreq&i ;
        output out=stats&i chisq;
        format &pred $Fm&i.t.;
    run;

%mend;

%mmacro1(sex,1);
%mmacro1(Chol_Status,2);
%mmacro1(Weight_Status,3);
%mmacro1(Smoking_Status,4);
%mmacro1(BP_Status,5);

[Astounding]

If you can tolerate a small change to the output, the simplest change would be to fool the software by inserting an extra blank.  Where you now have this:

proc format;
value $Ffmt ( notsorted)
'Desirable'='Desirable'
'Borderline'='Borderline'
'High'='High';
Run;

Change it to:

proc format;
value $Ffmt 
'Desirable'=' Desirable'
'Borderline'='Borderline'
'High'='High';
Run;

The rest of the program requires no changes, if the extra blank is acceptable.

[ballardw]

Another option to accomplish what you are asking may be to use a procedure that uses more of the formats information such as Proc Tabulate with the PRELOADFMT option. Maybe.

proc format library=work;
value $Ffmt ( notsorted)
'Desirable'='Desirable'
'Borderline'='Borderline'
'High'='High';
Run;


proc tabulate data=sashelp.heart ;
 class chol_status /preloadfmt  ;
 class status;
 format chol_status $ffmt.;
table Chol_Status,
      Status
    /printmiss;
run;

However not many procedures use Preloadfmt: Tabulate, Means/Summary and Report. So you may have to create statistics using other procedures such as Freq and then use one of Tabulate or Report to display them in order. Note that Tabulate requires additional options, such as the PRINTMISS above to display the values in the format order and use of other ORDER options may interfere with the order you want.

[Ronein]

Hello

When I run your code I get results  where the order of the categories is:

Bordelrine

Desirable

High

But I want to have same order of categories as in the proc format

Desirable

Bordelrine

High

 proc format library=work;
value $Ffmt ( notsorted)
'Desirable'='Desirable'
'Borderline'='Borderline'
'High'='High';
Run;
    proc tabulate data=sashelp.heart ;
 class chol_status /preloadfmt  ;
 class status;
 format chol_status $ffmt.;
table Chol_Status,
      Status
    /printmiss;
run;