topic Re: proc freq order=formatted in SAS Programming

proc freq order=formatted

Ronein — Wed, 02 Jan 2019 13:04:41 GMT

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;

Re: proc freq order=formatted

gamotte — Wed, 02 Jan 2019 14:18:10 GMT

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_sect006.htm

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);

Re: proc freq order=formatted

Astounding — Wed, 02 Jan 2019 15:07:03 GMT

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.

Re: proc freq order=formatted

ballardw — Thu, 03 Jan 2019 17:46:48 GMT

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.

Re: proc freq order=formatted

Ronein — Fri, 04 Jan 2019 14:43:40 GMT

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;