Need help in SGPLOT title with ODS

[bijayadhikar]

Hello SAS Users,

I am creating a line graph of 14 days trend data. I compare day 1 and 14 and assign three categories,

• Steady (grey color)= IF day 14 value is between 10% less and 10% more
• Rising (red color) =  increased by more than 10%
• Falling (green color) =when their averages have decreased by more than 10%   

I would like to show word "increase", decrease" or "steady" outside graph boarder (top left, outside), as well as arrow indicator as shows in the picture, attached.  Here is the data and codes

Data have;
Input RepDate cnt_11FNC avg_11FNC;
FORMAT Repdate mmddyy10.;
Datalines;
01/16/2021 1 4.3
01/17/2021 1 3.9
01/18/2021 12 5.3
01/19/2021 16 6.0
01/20/2021 2 5.4
01/2021/2021 5 5.9
01/22/2021 6 6.1
01/23/2021 4 6.6
01/24/2021 5 7.1
01/25/2021 2 5.7
01/26/2021 1 3.6
01/27/2021 4 3.9
01/28/2021 4 3.7
01/29/2021 7 3.9
;
run;

options nodate nonumber center;
title HEIGHT=20pt FONT='Times New Roman' bold "Test indicator";

ODS PDF FILE = '/file path....dashboard.pdf';
ods layout Start width=11.5in height=8.5in columns=1 rows=1
column_gutter=0in row_gutter=0.in row_heights=(.5in .5in);
options topmargin=0.5in orientation=landscape ;

ODS tagsets.sasreport13(ID=EGSR) gtitle;
ods region row=1 column=1 height=2 in width=2 in;
ods graphics / noborder;
title justify=left HEIGHT=12pt FONT='Times New Roman' bold "Far North Central";
title2 justify=left HEIGHT=12pt FONT='Times New Roman' bold "decrease" ;

proc sgplot data=revenue11 NOAUTOLEGEND;
vbar Report_Date_Received / response=cnt_11FNC nooutline dataskin=pressed fillattrs=( color=lightgrey);
vline Report_Date_Received / response=avg_11FNC lineattrs=(color=red thickness=4 PATTERN=SHORTDASH);
label league='League = Division E';
xaxis display=(nolabel) valueattrs=(size=6);
yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 25 by 5) offsetmin=0;

run;

ods layout end;
ods PDF close;

Than you in advance.

Bijay, Canada

[ballardw]

Where in your code do you calculate the "rising" "falling" or "steady" and the percentage?

What methodology is to be used?

Do those circles imply that you are just comparing the first and last displayed value?

[bijayadhikar]

Hello  ballardw,

Thanks for prompt response. You are correct that my percent change is substracting day 14th value from day 1 (fyi, my date value always comes from today's date-14, so I always have 14 days moving data. I wanted to provide %change calculation in sas data steps but could not calculate %change in the same column. One more point is that I have 13 variables from 3 regions to create 13 graphs but lets find solution for one or two, so I will apply same solution for others. Here is data showing in excel table showing calculation.

Report_Date_Received	avg_11FNC	%change_avg_11FNC	avg_12FNW	%change_avg_12FNW
16Jan2021	4.3	.	23.6	.
17Jan2021	3.9	.	24.0	.
18Jan2021	5.3	.	26.9	.
19Jan2021	6.0	.	30.1	.
20Jan2021	5.4	.	31.4	.
21Jan2021	5.9	.	27.3	.
22Jan2021	6.1	.	29.0	.
23Jan2021	6.6	.	29.9	.
24Jan2021	7.1	.	29.1	.
25Jan2021	5.7	.	26.4	.
26Jan2021	3.6	.	23.3	.
27Jan2021	3.9	.	25.0	.
28Jan2021	3.7	.	25.9	.
29Jan2021	3.9	=(3.9-4.3)*100	25.0	=(25.0-23.6)*100

[bijayadhikar]

Here is my datasets
data have;
input RepDate cnt_11FNC cnt_12FNW avg_11FNC avg_12FNW;
cards;
17Jan2021 1 28 4.3 23.6
18Jan2021 1 26 3.9 24.0
19Jan2021 12 35 5.3 26.9
20Jan2021 16 40 6.0 30.1
21Jan2021 2 17 5.4 31.4
22Jan2021 5 19 5.9 27.3
23Jan2021 6 38 6.1 29.0
24Jan2021 4 34 6.6 29.9
25Jan2021 5 21 7.1 29.1
26Jan2021 2 16 5.7 26.4
27Jan2021 1 18 3.6 23.3
28Jan2021 4 29 3.9 25.0
29Jan2021 4 25 3.7 25.9
30Jan2021 7 13 3.9 22.3
;
run;
/*need to calculate percent change for each variable subtracting value from day 14 to day 1 (e.g. [(3.9-4.3)*100] or [(25.0-23.6)*100].

Here is SG plot procedure;
options nodate nonumber center;
title HEIGHT=20pt FONT='Times New Roman' bold "Saskatchewan COVID-19 Dashboard";

ODS PDF FILE = '/SAS_data/research/CD_Epis/Bijay/dashboard/dashboard.pdf';
ods layout Start width=11.5in height=8.5in columns=5 rows=3
column_gutter=0in row_gutter=0.in row_heights=(.5in .5in);
options topmargin=0.5in orientation=landscape ;

ODS tagsets.sasreport13(ID=EGSR) gtitle;

ods region row=1 column=1 height=2 in width=2 in;
ods graphics / noborder;
title justify=left HEIGHT=12pt FONT='Times New Roman' bold "Far North Central";
title2 justify=left HEIGHT=12pt FONT='Times New Roman' bold ' ';
proc sgplot data=revenue11 NOAUTOLEGEND;
vbar RepDate / response=cnt_11FNC nooutline dataskin=pressed fillattrs=( color=lightgrey);
vline RepDate / response=avg_11FNC lineattrs=(color=red thickness=4 PATTERN=SHORTDASH);
label league='League = Division E';
xaxis display=(nolabel) valueattrs=(size=6);
yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 25 by 5) offsetmin=0;

run;

ods region row=1 column=2 height=2 in width=2 in;
ods graphics / noborder;
title justify=left HEIGHT=12pt FONT='Times New Roman' bold "Far North West";
title2 justify=left HEIGHT=12pt FONT='Times New Roman' bold " " ;

proc sgplot data=revenue11 NOAUTOLEGEND ;

vbar RepDate / response=cnt_12FNW nooutline dataskin=pressed fillattrs=( color=lightgrey);
vline RepDate / response=avg_12FNW lineattrs=(color=red thickness=4 PATTERN=SHORTDASH);
label league='League = Division W';

xaxis display=(nolabel) valueattrs=(size=6);
yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 50 by 10) offsetmin=0;
run;
ods layout end;
ods PDF close;

Thanks

[bijayadhikar]

Hi, I am waiting if someone knowledgeable could help me here. If further clarification if needed on my question please let  me know. I believe there must be a solution since SAS is such a powerful tool.

Thanks

Bijay

[ballardw]

I strongly suggest that you add a variable that indicates which "league" a record represents and only have one plot variable instead of hiding the "league" information in the name of the plot variables.

Then it will be possible to use a single SGplot, with a By statement, or possibly SGPanel to and that variable as a Panelby variable.

That will also make it easier to get the information such as the comparisons you request.

An example with added fake data and a basic SGPanel plot using that data plus a data set showing how to get the additional change information. Adding text with conditional colors and unicode symbols is tricky though.

Data have;
Input RepDate :mmddyy10. cnt_11FNC avg_11FNC league :$10.;
FORMAT Repdate mmddyy10.;
Datalines;
01/16/2021 1 4.3  South
01/17/2021 1 3.9  South
01/18/2021 12 5.3  South
01/19/2021 16 6.0  South
01/20/2021 2 5.4  South
01/21/2021 5 5.9  South
01/22/2021 6 6.1  South
01/23/2021 4 6.6  South
01/24/2021 5 7.1  South
01/25/2021 2 5.7  South
01/26/2021 1 3.6  South
01/27/2021 4 3.9  South
01/28/2021 4 3.7  South
01/29/2021 7 3.9  South
01/16/2021 1 5.3  North
01/17/2021 1 6.9  North
01/18/2021 12 6.3  North
01/19/2021 16 9.0  North
01/20/2021 2 7.4  North
01/21/2021 5 8.9  North
01/22/2021 6 6.1  North
01/23/2021 4 6.6  North
01/24/2021 5 7.1  North
01/25/2021 2 4.7  North
01/26/2021 1 5.6  North
01/27/2021 4 5.9  North
01/28/2021 4 5.7  North
01/29/2021 7 13.9  North
;
run;

proc sort data=have;
   by league repdate;
run;
/* this works when the data has been filtered
   to the appropriate date ranges
*/

proc sgpanel data=have NOAUTOLEGEND;
   panelby league /columns=3;
   vbar RepDate / response=cnt_11FNC
                 nooutline dataskin=pressed 
                 fillattrs=( color=lightgrey)
   ;
   vline RepDate / response=avg_11FNC 
                   lineattrs=(color=red thickness=4 PATTERN=SHORTDASH);
   label league='League = Division E';
   colaxis display=(nolabel) valueattrs=(size=6);
   rowaxis display=(nolabel) valueattrs=(size=7) values=(0 to 25 by 5) offsetmin=0;

run;

data change;
   set have;
   by league;
   retain firstval;
   if first.league then firstval= avg_11FNC;
   if last.league then do;
      Change = avg_11FNC - firstval;
      percentchange = change/firstval;
      output;
   end;
run;

Or SGPlot with By:

proc sgplot data=have NOAUTOLEGEND;
   by league ;
   vbar RepDate / response=cnt_11FNC
                 nooutline dataskin=pressed 
                 fillattrs=( color=lightgrey)
   ;
   vline RepDate / response=avg_11FNC 
                   lineattrs=(color=red thickness=4 PATTERN=SHORTDASH);
   label league='League = Division E';
   xaxis display=(nolabel) valueattrs=(size=6);
   yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 25 by 5) offsetmin=0;

run;

[bijayadhikar]

Thanks ballardw for offering your insights. I will post tonight complete dataset and steps, that probably will give more insights  to think about if symbols or colors are possible. I have small 13 graphs in one page using ODS as a concept of dashboard, though its not a fancy, but thinking if this is even possible.

Will post more soon!

[Rick_SAS]

Two thoughts:

1. The simplest solution is to have one data set and use a BY statement to create the multiple plots. Then you can use the BY values on the TITLE statement, like the following (for simplicity, all data is the same and I manually set the trend):

Data have;
length Region $6; 
Input Region RepDate cnt_11FNC avg_11FNC;
informat RepDate ANYDTDTE10.;
FORMAT Repdate mmddyy10.;
Datalines;
South 01/16/2021 1 4.3
South 01/17/2021 1 3.9
South 01/18/2021 12 5.3
South 01/19/2021 16 6.0
South 01/20/2021 2 5.4
South 01/21/2021 5 5.9
South 01/22/2021 6 6.1
South 01/23/2021 4 6.6
South 01/24/2021 5 7.1
South 01/25/2021 2 5.7
South 01/26/2021 1 3.6
South 01/27/2021 4 3.9
South 01/28/2021 4 3.7
South 01/29/2021 7 3.9
West  01/16/2021 1 4.3
West  01/17/2021 1 3.9
West  01/18/2021 12 5.3
West  01/19/2021 16 6.0
West  01/20/2021 2 5.4
West  01/21/2021 5 5.9
West  01/22/2021 6 6.1
West  01/23/2021 4 6.6
West  01/24/2021 5 7.1
West  01/25/2021 2 5.7
West  01/26/2021 1 3.6
West  01/27/2021 4 3.9
West  01/28/2021 4 3.7
West  01/29/2021 7 3.9
North 01/16/2021 1 4.3
North 01/17/2021 1 3.9
North 01/18/2021 12 5.3
North 01/19/2021 16 6.0
North 01/20/2021 2 5.4
North 01/21/2021 5 5.9
North 01/22/2021 6 6.1
North 01/23/2021 4 6.6
North 01/24/2021 5 7.1
North 01/25/2021 2 5.7
North 01/26/2021 1 3.6
North 01/27/2021 4 3.9
North 01/28/2021 4 3.7
North 01/29/2021 7 3.9
;

proc sort data=Have;
by Region RepDate;
run;

/* Do analysis to find trend. */
data Trend;
length Region $6 Trend $10; 
input Region Trend;
datalines;
North Decreasing
South Increasing
West  Neutral
;

/* Merge results with original data */
data Want;
merge Have Trend;
by Region;
run;

/* layout the graphs. Use the #BYVALn values to build the titles */
ods graphics / width=300px height=250px;
ods layout gridded columns=3 advance=table;

options nobyline;
title "Counts by Region";
title2 "#byval1 Region Is #byval2";

proc sgplot data=Want noautolegend;
by Region Trend notsorted;
vbar RepDate / response=cnt_11FNC;
vline RepDate / response=avg_11FNC;
run;

options byline;
ods layout end;
title;

2. For more complicated graphs where colors/symbols vary between groups, you might want to use PROC SGPANEL.

[bijayadhikar]

Hi Rick_SAS

Thank you very much for your time and suggestion. In particular I like your idea of using 'hastag byval' option outputting at least what I wanted (increase, decrease or neutral). I am sure  I can put other text e.g. -%5 or 7% etc with the similar concept. 

The only remaining part is inserting up or down arrow, which seems not feasible in sgplot.

I will be back after soon....

[Rick_SAS]

> The only remaining part is inserting up or down arrow, which seems not feasible in sgplot.

SAS has supported unicode symbols for a long time. And PROC SGPLOT has supported unicode for at least 10 years. Sometimes people don't have unicode fonts installed, but try the following examples. On my PC, I only get the side arrow in HTML, but in other ODS destination (for example, RTF) I can see all the symbols:

data Have;
do x = 1 to 10;
   yUp = x;
   yDown = -1;
   ySide = 0;
   output;
end;
run;

ods escapechar='~';
ods RTF;

title "This is increasing ~{unicode '2197'x}";
proc sgplot data=Have;
   series x=x y=yUp;
run;
title "This is decreasing ~{unicode '2198'x}";
proc sgplot data=Have;
   series x=x y=yDown;
run;
title "This is neutral ~{unicode '2192'x}";
proc sgplot data=Have;
   series x=x y=ySide;
run;

ods RTF close;

Unicode symbols are also supported as tick values, labels, and in legends. See

https://blogs.sas.com/content/graphicallyspeaking/2011/11/14/the-power-of-unicode/

for an introduction.

[bijayadhikar]

Hello Rick @Rick_SAS 

Thank you for your time and support. Very excited to be connected with the forum. Although I do not have high level skills but never quite to learn SAS. Your tips helped me to understand about unencodes. I tried to apply in my situation but appears either it is not supported totally in the way I am doing or I may not have data in right orientation. I work is to create a 13 graphs in a page using OSD pdf and I am mostly successful. See picture attached just example. However I am still trying to use unicodes for up and down arrows as per your tips. But in my cases only up arrow is coming ignoring increase or decreased pattern. I am attaching sas dataset if you could find a moment to look on to this.

Happy to provide more info if needed. In the sas dataset I have only given 3 regions out of 13 regions I have. Here is the sas codes,

Data main;
set zone;
run;

Data newvar;
set main;
IF change>(10.0) then yUp=1;
else IF change<(-11.0) then yDown=-1;
else ySide=0.00;
run;

ods escapechar='~';
ods RTF;

options nobyline;
title justify=center HEIGHT=15pt FONT='Times New Roman' color=darkgray bold "#BYVAL1" ;
title2 justify=center HEIGHT=12pt FONT='Calibri' color=big bold "#BYVAL2" " " "~{unicode '2197'x}";

proc sgplot data=newvar NOAUTOLEGEND;
by Region trend notsorted;
where Region='Region12';

vbarparm category=RepDate response=Count / dataskin=none ;
series x=RepDate y=mov_aver ;
xaxis display=(nolabel) valueattrs=(size=6);
yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 40 by 5) offsetmin=0;

run;

options nobyline;
title justify=center HEIGHT=15pt FONT='Times New Roman' color=darkgray bold "#BYVAL1" ;
title2 justify=center HEIGHT=12pt FONT='Calibri' color=big bold "#BYVAL2" " " "~{unicode '2197'x}";

proc sgplot data=newvar NOAUTOLEGEND;
by Region trend notsorted;
where Region='Region20';

vbarparm category=RepDate response=Count / dataskin=none ;
series x=RepDate y=mov_aver ;
xaxis display=(nolabel) valueattrs=(size=6);
yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 15 by 5) offsetmin=0;

run;

options nobyline;
title justify=center HEIGHT=15pt FONT='Times New Roman' color=darkgray bold "#BYVAL1" ;
title2 justify=center HEIGHT=12pt FONT='Calibri' color=big bold "#BYVAL2" " " "~{unicode '2197'x}";

proc sgplot data=newvar NOAUTOLEGEND;
by Region trend notsorted;
where Region='Region21';

vbarparm category=RepDate response=Count / dataskin=none ;
series x=RepDate y=mov_aver ;
xaxis display=(nolabel) valueattrs=(size=6);
yaxis display=(nolabel) valueattrs=(size=7) values=(0 to 20 by 5) offsetmin=0;

run;

Thanks - Bijay

[bijayadhikar]

Thank you ballardw for your time and suggestion. I am taking some of your suggestion and thinking over possible solution. Probably text/color feature is limited in SGplot. However, I will soon explore about sgpanel too.

[BrunoMueller]

I suggest to combine the already provided code samples with the programming pattern described here https://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/ by @ChrisHemedinger 

this allows you to have individual control on how to display each region.

See below for an example, the code was run in SAS Enterprise Guide with HTML as the default output result.

Data have;
  length Region $6;
  Input Region RepDate cnt_11FNC avg_11FNC;
  informat RepDate ANYDTDTE10.;
  FORMAT Repdate mmddyy10.;
  Datalines;
South 01/16/2021 1 4.3
South 01/17/2021 1 3.9
South 01/18/2021 12 5.3
South 01/19/2021 16 6.0
South 01/20/2021 2 5.4
South 01/21/2021 5 5.9
South 01/22/2021 6 6.1
South 01/23/2021 4 6.6
South 01/24/2021 5 7.1
South 01/25/2021 2 5.7
South 01/26/2021 1 3.6
South 01/27/2021 4 3.9
South 01/28/2021 4 3.7
South 01/29/2021 7 3.9
West  01/16/2021 1 4.3
West  01/17/2021 1 3.9
West  01/18/2021 12 5.3
West  01/19/2021 16 6.0
West  01/20/2021 2 5.4
West  01/21/2021 5 5.9
West  01/22/2021 6 6.1
West  01/23/2021 4 6.6
West  01/24/2021 5 7.1
West  01/25/2021 2 5.7
West  01/26/2021 1 3.6
West  01/27/2021 4 3.9
West  01/28/2021 4 3.7
West  01/29/2021 7 3.9
North 01/16/2021 1 4.3
North 01/17/2021 1 3.9
North 01/18/2021 12 5.3
North 01/19/2021 16 6.0
North 01/20/2021 2 5.4
North 01/21/2021 5 5.9
North 01/22/2021 6 6.1
North 01/23/2021 4 6.6
North 01/24/2021 5 7.1
North 01/25/2021 2 5.7
North 01/26/2021 1 3.6
North 01/27/2021 4 3.9
North 01/28/2021 4 3.7
North 01/29/2021 7 3.9
East 01/16/2021 1 4.3
East 01/17/2021 1 3.9
East 01/18/2021 12 5.3
East 01/19/2021 16 6.0
East 01/20/2021 2 5.4
East 01/21/2021 5 5.9
East 01/22/2021 6 6.1
East 01/23/2021 4 6.6
East 01/24/2021 5 7.1
East 01/25/2021 2 5.7
East 01/26/2021 1 3.6
East 01/27/2021 4 3.9
East 01/28/2021 4 3.7
East 01/29/2021 7 3.9
;

proc sort data=Have;
  by Region RepDate;
run;

/* Do analysis to find trend. */
data Trend;
  length Region $6 Trend $ 10;
  input Region Trend;
  datalines;
East Decreasing
North Decreasing
South Increasing
West  Neutral
;

/* Merge results with original data */
data Want;
  merge Have Trend;
  by Region;
run;

ods escapechar="^";

%macro plots;
  %local i nObs region trend ;
  title "Counts by Region";
  ods graphics / width=300px height=250px;
  ods layout gridded columns=3;

  proc sql noprint;
    select distinct
      region
      , trend
    into
      :regionValue1 -
      , :trendValue1 -
    from
      want
    ;
    %let nObs = &sqlobs;
  quit;

  %do i = 1 %to &nObs;
    %let region = &&regionValue&i;
    %let trend = &&trendValue&i;
    %put NOTE: &sysmacroname processing &i of &nobs &=region &=trend;
    %let title2line =;

ods region;
  title1 "&region";

    /*title "Counts by Region";*/
    %if &trend = Decreasing %then
      %do;
        title2 c=red "&trend^{unicode 2198}";
      %end;

    %if &trend = Increasing %then
      %do;
        title2 c=blue "&trend^{unicode 2197}";
      %end;

    %if &trend = Neutral %then
      %do;
        title2 c=black "&trend^{unicode 2192}";
      %end;

  proc sgplot data=Want noautolegend;
    where region = "&region";
    vbar RepDate / response=cnt_11FNC;
    vline RepDate / response=avg_11FNC;
  run;

  %end;

ods layout end;
title;
%mend;

%plots

[bijayadhikar]

@BrunoMueller  I reviewed the documentation by Chris Hemednger whiich will be useful for BY option to produce my 13 regions.

I executed the code you have provided, it did run and produce 4 graphs, however it did not write ODS region title (i.e. Region name and trend). Is there an error in the syntax or I am missing something here. 

[BrunoMueller]

This is the output in HTML I get:

What kind of output type are you using?