SAS SGPLOT yaxis discreteorder=formatted: Force numerical sorting instead of lexigraphical

Question

I'm creating a heatmap with discrete X and discrete Y axis. The discrete Y axis is numerical (BEST12.) but yaxis discreteorder=formatted and discreteorder=unformatted are both sorting the variable lexigraphicly.

Expected: 1 2 3 4 5 ... 10 11 12 ... 20 Actual: 1 10 11 ... 2 20 21 ....

How do I force a numerical sort order?

proc sgplot data=Heatmap;
yaxis discreteorder=formatted;
    heatmap x=Battalion y=StationArea / name='HeatMap' discretey discretex
        colorresponse=arrive_seconds colorstat=mean;
    gradlegend 'HeatMap';
run;

**EDIT for Stu's suggestion to use 'proc sort' and then omit 'discreteorder' :

The result then is that the first values on the y axis are only the StationAreas that B01 have responded to.

Answer 1

Sort your data by StationArea Battalion, then create the heatmap without the yaxis discreteorder option.

proc sort data=heatmap;
    by StationArea Battalion;
run;

proc sgplot data=Heatmap;
    heatmap x=Battalion y=StationArea / name='HeatMap' discretey discretex
        colorresponse=arrive_seconds colorstat=mean;
    gradlegend 'HeatMap';
run;

If you have Battalions that go above 10, you can ensure it stays in the right order by extracting the number and sorting by it. You can extract the number with battalion_nbr = input(substr(Battalion, 2, 2), 8.);. For example:

data heatmap;
    format StationArea BEST12.;
    call streaminit(1234);

    do StationArea = 1 to 50;
        do Battalion = 'B01', 'B02', 'B03', 'B04', 'B05', 'B06',
                       'B07', 'B08', 'B09', 'B10', 'B11', 'B99'
        ;
            battalion_nbr  = input(substr(Battalion, 2, 2), 8.);
            arrive_seconds = abs(rand('normal'));
            output;
        end;
    end;
run;

proc sort data=heatmap;
    by StationArea battalion_nbr;
run;

proc sgplot data=Heatmap;
    heatmap x=Battalion y=StationArea / name='HeatMap' discretey discretex
        colorresponse=arrive_seconds colorstat=mean;
    gradlegend 'HeatMap';
run;

Answer 2

Try filling in the missing cells so that all of the Battalion values are present on the first StationArea. Now the default DATA order should work.

proc sql ;
   create table skeleton as select * 
   from (select distinct StationArea from heatmap)
      , (select distinct Battalion from heatmap)
   order by StationArea, Battalion
   ;
run;

proc sort data=heatmap;
    by StationArea Battalion;
run;

data heatmap_full;
   merge heatmap skeleton;
   by StationArea Battalion;
run;