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.
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)
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)
Can you describe the order that you are getting?
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.