CODEWITHSUNDEEP
sasheatmapsgplot
AlexStudentCSU
AlexStudentCSU

Reputation: 21

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

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.

Upvotes: 1

Views: 446

Answers (2)

Tom
Tom

Reputation: 51591

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;

Upvotes: 1

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

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

Upvotes: 0

Related Questions