How to sort by formatted values

Question

proc sort data=sas.mincome;
    by F3 F4;
run;

Proc sort doesn't sort the dataset by formatted values, only internal values. I need to sort by two variables prior to a merge. Is there anyway to do this with proc sort?

Answer 1

I don't think you can sort by formatted values in proc sort, but you can definitely use a simple proc SQL procedure to sort a dataset by formatted values. proc SQL is similar to the data step and proc sort, but is more powerful.

The general syntax of proc sql for sorting by formatted values will be:

proc sql;
    create table NewDataSet as
    select variable(s)
    from OriginalDataSet
    order by put(variable1, format1.), put(variable2, format2.);

quit;

For example, we have a sample data set containing the names, sex and ages of some people and we want to sort them:

proc format;
    value gender 1='Male'
                 2='Female';
    value age    10-15='Young'
                 16-24='Old';
    run;


data work.original;
    input name $ sex age;
    datalines;
    John 1 12
    Zack 1 15
    Mary 2 18
    Peter 1 11
    Angela 2 24
    Jack 1 16
    Lucy 2 17
    Sharon 2 12
    Isaac 1 22
    ;
run;

proc sql;
    create table work.new as
    select name, sex format=gender., age format=age. 
    from work.original
    order by put(sex, gender.), put(age, age.);
quit;

Output of work.new will be:

                             Obs     name      sex       age

                              1     Mary      Female    Old
                              2     Angela    Female    Old
                              3     Lucy      Female    Old
                              4     Sharon    Female    Young
                              5     Jack      Male      Old
                              6     Isaac     Male      Old
                              7     John      Male      Young
                              8     Zack      Male      Young
                              9     Peter     Male      Young

If we had used proc sort by sex, then Males would have been ranked first because we had used 1 to represent Males and 2 to represent Females which is not what we want. So, we can clearly see that proc sql did in fact sort them according to the formatted values (Females first, Males second).

Hope this helps.

Answer 2

Because of the nature of formats, SAS only uses the underlying values for the sort. To my knowledge, you cannot change that (unless you want to build your own translation table via PROC TRANTAB).

What you can do is create a new column that contains the formatted value. Then you can sort on that column.

proc format library=work;
    value $test 'z' = 'a'
                'y' = 'b'
                'x' = 'c';
run;

data test;
format val $test.;
informat val $1.;
input val $;

val_fmt = put(val,$test.);
datalines;
x
y
z
;
run;

proc print data=test(drop=val_fmt);
run;

proc sort data=test;
by val_fmt;
run;

proc print data=test(drop=val_fmt);
run; 

Produces

                                    Obs    val

                                     1      c
                                     2      b
                                     3      a



                                    Obs    val

                                     1      a
                                     2      b
                                     3      c