Order Y variable in PROC SGPANEL heat map

[JacquesR]

So as not to repeat all the data, see this link:

https://communities.sas.com/t5/SAS-Programming/SGPANEL-heat-map-with-missing-cells/m-p/794901 

The heatmap is working well, but my one remaining problem is the ordering of the variables on the Y axis of the heat map.

The code as it now stands, is:

PROC FORMAT;
	VALUE $ Subs
    'facebook'      =' 1 Facebook'
    'youtube'       =' 2 YouTube'
    'weather'       =' 3 weather'
    'cricket'       =' 4 cricket'
    'superannuat',
    'superannuation'=' 5 superannuation'
    'codeine'       =' 6 codeine'
    'mdma'          =' 7 MDMA'
    'fentanyl'      =' 8 fentanyl'
    'clenbuterol'   =' 9 clenbuterol'
    'modafinil'     ='10 modafinil'
    'pregabalin'    ='11 pregabalin'
	;
RUN;

%MACRO CommonCode(dset=);
DATA &dset.; SET &dset.; FORMAT _Substance Subs.;run;
proc print data=&dset.;run;
PROC SGPANEL
    NOAUTOLEGEND
    DATA=&dset.;
    PANELBY _Scale _Region /LAYOUT=LATTICE NOVARNAME ONEPANEL NOWALL NOBORDER;
    ROWAXIS DISPLAY=(NOLABEL) DISCRETEORDER=FORMATTED;
    COLAXIS DISPLAY=(NOLABEL) DISCRETEORDER=FORMATTED;
    HEATMAP X=SW_Signif1 Y=_Substance / COLORRESPONSE=PCT DISCRETEX DISCRETEY ;
    TEXT X=SW_Signif1 Y=_Substance TEXT=Pct;
    FORMAT Pct PERCENT8.2;
	TITLE "&dset.";
RUN;
%MEND CommonCode;
%CommonCode(dset=One);
%CommonCode(dset=Two);

I have used numbers in the format to indicate the desired order (using this format and statements like ORDER=FORMATTED in proc tabulate puts each item in the correct order).

The row axis and column axis of the panel are correctly ordered.

The Y axis, however, is ordered in some weird way that I cannot discern.

I have tried sorting the dataset, I have tried creating a numeric variable in the place of _Substance which reflects the correct order, neither of these worked.

I cannot see anything in the help files that reflects a way to sort the heat map (for example, the GROUPORDER=ASCENDING option with plots like VBOX).

How can I get the Y axis values to appear in the order specified from the format?

I have seen that if I change the uniscale= option (e.g., uniscale=row) on the panelby statement, I can change the order of the Y axis, but still not to reflect what I need.

[Ksharp · Accepted solution]

Add REVERSE option:

%MACRO CommonCode(dset=);
DATA &dset.; SET &dset.; FORMAT _Substance Subs.;run;
PROC SGPANEL
    NOAUTOLEGEND
    DATA=&dset.;
    PANELBY _Scale _Region /LAYOUT=LATTICE NOVARNAME ONEPANEL NOWALL NOBORDER;
    ROWAXIS DISPLAY=(NOLABEL) DISCRETEORDER=FORMATTED reverse ;
    COLAXIS DISPLAY=(NOLABEL) DISCRETEORDER=FORMATTED;
    HEATMAP X=SW_Signif1 Y=_Substance / COLORRESPONSE=PCT DISCRETEX DISCRETEY ;
    TEXT X=SW_Signif1 Y=_Substance TEXT=Pct;
    FORMAT Pct PERCENT8.2;
	TITLE "&dset.";
RUN;
%MEND CommonCode;
%CommonCode(dset=One)

[DanH_sas]

Can you describe the order that you are getting?

[JacquesR]

With quite some egg on my face, I have to admit that I realised today that part of my problem was the y axis fit policy. Once I increased the height of the axis, it was showing me what I wanted to see, with the Reverse option added.

Thanks