1

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;

enter image description here enter image description here

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

enter image description here

enter image description here

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

AlexStudentCSU
  • 21
  • 2
  • Sorting by STATIONAREA should work for your example. Set Yaxis ordering to DATA since you have data for every row in the map. And formatted should work for the Xaxis because with the leading zeros the lexical ordering is the numeric ordering. – Tom Aug 11 '23 at 14:42
  • Why did you post your data as a photograph? Just type the 14 numbers into your question and then others can try recreating your issue using your data. – Tom Aug 11 '23 at 15:02

2 Answers2

0

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;

enter image description here

Stu Sztukowski
  • 10,597
  • 1
  • 12
  • 21
  • Your answer works if there's an intersection for each StationArea for each Battalion. Since not all Battalions have responded to all StationAreas, omitting the discreteorder=formatted leaves the default of discreteorder=data. If B01 has only responded to StationAreas 1, 5, 7, and 10, then the first four on the yaxis will be 1, 5, 7, 10. I'm adding a screenshot of that to my post. – AlexStudentCSU Aug 11 '23 at 14:27
  • @AlexStudentCSU That would just impact the X axis. Since your formatted values of BATTALION sort properly as strings just use formatted ordering for the X axis and leave data ordering for the Y axis. – Tom Aug 12 '23 at 01:16
0

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;
Tom
  • 47,574
  • 2
  • 16
  • 29