Hello SAS Users,
I am creating a line graph of 14 days trend data. I compare day 1 and 14 and assign three categories,
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
From the log one can see, that you are using the SAS Report ODS destination. This one does not support gridded layout.
I made some changes to the code:
Here is the code:
Data want;
length Region trend $ 16;
call streaminit(1234);
do n = 1 to 13;
region = cats("Region", put(n, z2.));
trend = choosec(rand("integer",1, 3), "Increasing", "Decreasing", "Neutral");
do repDate = 1 to 7;
cnt_11fnc = rand("integer", 1, 16);
avg_11fnc = rand("uniform") * 8;
output;
end;
end;
format
repdate 2.
cnt_11fnc 3.
avg_11fnc 4.1
;
run;
%macro plots;
%local i nObs region trend;
proc sql noprint;
select distinct
region
, trend
into
:regionValue1 -
, :trendValue1 -
from
want
;
%let nObs = &sqlobs;
quit;
%do i = 1 %to &nObs;
%let region = &®ionValue&i;
%let trend = &&trendValue&i;
%put NOTE: &sysmacroname processing &i of &nobs &=region &=trend;
ods region;
/* title1 "®ion";*/
%if &trend = Decreasing %then
%do;
title1 "®ion" c=red "&trend^{unicode '2198'x}";
%end;
%if &trend = Increasing %then
%do;
title1 "®ion" c=blue "&trend^{unicode '2197'x}";
%end;
%if &trend = Neutral %then
%do;
title1 "®ion" c=black "&trend^{unicode '2192'x}";
%end;
proc sgplot data=Want noautolegend;
where region = "®ion";
vbar RepDate / response=cnt_11FNC;
vline RepDate / response=avg_11FNC;
run;
%end;
ods layout end;
title;
%mend;
ods _all_ close;
ods escapechar="^";
options orientation=landscape papersize="A3" ;
ods pdf file="c:\temp\report.pdf" nogtitle;
title "Counts by Region";
ods graphics / width=72mm height=65mm;
ods layout gridded columns=5 column_gutter=5mm;
%plots
ods pdf close;
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?
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 |
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
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;
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!
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.
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....
> 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.
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
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 = &®ionValue&i;
%let trend = &&trendValue&i;
%put NOTE: &sysmacroname processing &i of &nobs &=region &=trend;
%let title2line =;
ods region;
title1 "®ion";
/*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 = "®ion";
vbar RepDate / response=cnt_11FNC;
vline RepDate / response=avg_11FNC;
run;
%end;
ods layout end;
title;
%mend;
%plots
@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.
This is the output in HTML I get:
What kind of output type are you using?
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.