Partly Bar - Partly Line graph using GTL instead of Sgpanel?

[venkiparki_gmail_com]

Hi all,

I need help with the bar-line graph. Attached file has the graph I produced using sgpanel.

I hid some of the titles, but you can see the sgpanel graph clearly. The data for that graph is pasted below.

I used the code below to create that graph. I wanted to keep the bars in the graph separately by YTDActual and YTDGoal.

I am having issues doing that using sgpanel. I am not really familiar with Proc template. All the codes I have seen in Proc template uses complete overlay of bar-line and not part of line and part of bar in the same graph. Can anyone please help me do this using Proc template? I also want the legend should show for all line and bar graphs below.

If I get the code right, I have a macro that does this for each employee and send an email with their report by their ID.

Thanks very much!

Proc sgpanel data=kpiGoalActualYTD;

  panelby  KPI /  novarname;

  vline Month /group=Yr2014 response=Score lineattrs=(thickness=2) stat=mean;

  vbar Month / group=Yr2014 response=YTDActual  stat=mean  transparency=0.3;

  vbar Month /group=Yr2014 response=YTDGoal stat=mean  barwidth=0.5 transparency=0.2;

  rowaxis grid values=(60 to 100 by 10)  label='Score';

  colaxis grid DISCRETEORDER= DATA;

run;

Leader	ID	Yr2014	KPI	Month	Score	Monthnum	YTDActual	YTDGoal
LeaderName	LeaderID	Actual	FirstKPI	Jan	87.4	1	.	.
LeaderName	LeaderID	Actual	FirstKPI	Feb	87.3	2	.	.
LeaderName	LeaderID	Actual	FirstKPI	Mar	88.3	3	.	.
LeaderName	LeaderID	Actual	FirstKPI	Apr	87.8	4	.	.
LeaderName	LeaderID	Goal	FirstKPI	Jan	89.3	1	.	.
LeaderName	LeaderID	Goal	FirstKPI	Feb	88.4	2	.	.
LeaderName	LeaderID	Goal	FirstKPI	Mar	88.9	3	.	.
LeaderName	LeaderID	Goal	FirstKPI	Apr	89.6	4	.	.
LeaderName	LeaderID	Goal	FirstKPI	May	90	5	.	.
LeaderName	LeaderID	Goal	FirstKPI	Jun	88.7	6	.	.
LeaderName	LeaderID	Goal	FirstKPI	Jul	87	7	.	.
LeaderName	LeaderID	Goal	FirstKPI	Aug	89.4	8	.	.
LeaderName	LeaderID	Goal	FirstKPI	Sep	90.3	9	.	.
LeaderName	LeaderID	Goal	FirstKPI	Oct	90.3	10	.	.
LeaderName	LeaderID	Goal	FirstKPI	Nov	91.2	11	.	.
LeaderName	LeaderID	Goal	FirstKPI	Dec	89.3	12	.	.
LeaderName	LeaderID	Goal	FirstKPI	YTD	.	.	87.7	89.4
LeaderName	LeaderID	Actual	SecondKPI	Jan	100	1	.	.
LeaderName	LeaderID	Actual	SecondKPI	Feb	100	2	.	.
LeaderName	LeaderID	Actual	SecondKPI	Mar	99.9	3	.	.
LeaderName	LeaderID	Actual	SecondKPI	Apr	100	4	.	.
LeaderName	LeaderID	Goal	SecondKPI	Jan	100	1	.	.
LeaderName	LeaderID	Goal	SecondKPI	Feb	100	2	.	.
LeaderName	LeaderID	Goal	SecondKPI	Mar	100	3	.	.
LeaderName	LeaderID	Goal	SecondKPI	Apr	100	4	.	.
LeaderName	LeaderID	Goal	SecondKPI	May	100	5	.	.
LeaderName	LeaderID	Goal	SecondKPI	Jun	98.9	6	.	.
LeaderName	LeaderID	Goal	SecondKPI	Jul	99.7	7	.	.
LeaderName	LeaderID	Goal	SecondKPI	Aug	100	8	.	.
LeaderName	LeaderID	Goal	SecondKPI	Sep	100	9	.	.
LeaderName	LeaderID	Goal	SecondKPI	Oct	100	10	.	.
LeaderName	LeaderID	Goal	SecondKPI	Nov	100	11	.	.
LeaderName	LeaderID	Goal	SecondKPI	Dec	100	12	.	.
LeaderName	LeaderID	Goal	SecondKPI	YTD	.	.	100	99.9
LeaderName	LeaderID	Actual	ThirdKPI	Jan	95.8	1	.	.
LeaderName	LeaderID	Actual	ThirdKPI	Feb	95.9	2	.	.
LeaderName	LeaderID	Actual	ThirdKPI	Mar	97.1	3	.	.
LeaderName	LeaderID	Actual	ThirdKPI	Apr	98.2	4	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Jan	97.6	1	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Feb	98.1	2	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Mar	98.1	3	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Apr	97.1	4	.	.
LeaderName	LeaderID	Goal	ThirdKPI	May	97.1	5	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Jun	96.4	6	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Jul	96	7	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Aug	95.8	8	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Sep	95.9	9	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Oct	96.5	10	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Nov	97.5	11	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Dec	96.2	12	.	.
LeaderName	LeaderID	Goal	ThirdKPI	YTD	.	.	96.7	96.8
LeaderName	LeaderID	Actual	FourthKPI	Jan	99.4	1	.	.
LeaderName	LeaderID	Actual	FourthKPI	Feb	94.4	2	.	.
LeaderName	LeaderID	Actual	FourthKPI	Mar	83.9	3	.	.
LeaderName	LeaderID	Actual	FourthKPI	Apr	100	4	.	.
LeaderName	LeaderID	Goal	FourthKPI	Jan	100	1	.	.
LeaderName	LeaderID	Goal	FourthKPI	Feb	100	2	.	.
LeaderName	LeaderID	Goal	FourthKPI	Mar	100	3	.	.
LeaderName	LeaderID	Goal	FourthKPI	Apr	100	4	.	.
LeaderName	LeaderID	Goal	FourthKPI	May	100	5	.	.
LeaderName	LeaderID	Goal	FourthKPI	Jun	100	6	.	.
LeaderName	LeaderID	Goal	FourthKPI	Jul	99.1	7	.	.
LeaderName	LeaderID	Goal	FourthKPI	Aug	99.1	8	.	.
LeaderName	LeaderID	Goal	FourthKPI	Sep	99.1	9	.	.
LeaderName	LeaderID	Goal	FourthKPI	Oct	98.8	10	.	.
LeaderName	LeaderID	Goal	FourthKPI	Nov	99.1	11	.	.
LeaderName	LeaderID	Goal	FourthKPI	Dec	100	12	.	.
LeaderName	LeaderID	Goal	FourthKPI	YTD	.	.	94.4	99.3

[DanH_sas · Accepted solution]

The problem is that you have both bars bound to the same midpoint value (YTD). To get the look that you want, Use two midpoints (YTDActual and YTDGoal) and associate the correct values for them. The data will look something like this:

Leader	ID	Yr2014	KPI	Month	Score	Monthnum	YTDActual	YTDGoal
LeaderName	LeaderID	Actual	FirstKPI	Jan	87.4	1	.	.
LeaderName	LeaderID	Actual	FirstKPI	Feb	87.3	2	.	.
LeaderName	LeaderID	Actual	FirstKPI	Mar	88.3	3	.	.
LeaderName	LeaderID	Actual	FirstKPI	Apr	87.8	4	.	.
LeaderName	LeaderID	Goal	FirstKPI	Jan	89.3	1	.	.
LeaderName	LeaderID	Goal	FirstKPI	Feb	88.4	2	.	.
LeaderName	LeaderID	Goal	FirstKPI	Mar	88.9	3	.	.
LeaderName	LeaderID	Goal	FirstKPI	Apr	89.6	4	.	.
LeaderName	LeaderID	Goal	FirstKPI	May	90	5	.	.
LeaderName	LeaderID	Goal	FirstKPI	Jun	88.7	6	.	.
LeaderName	LeaderID	Goal	FirstKPI	Jul	87	7	.	.
LeaderName	LeaderID	Goal	FirstKPI	Aug	89.4	8	.	.
LeaderName	LeaderID	Goal	FirstKPI	Sep	90.3	9	.	.
LeaderName	LeaderID	Goal	FirstKPI	Oct	90.3	10	.	.
LeaderName	LeaderID	Goal	FirstKPI	Nov	91.2	11	.	.
LeaderName	LeaderID	Goal	FirstKPI	Dec	89.3	12	.	.
LeaderName	LeaderID	Goal	FirstKPI	YTDGoal	.	.	.	89.4
LeaderName	LeaderID	Actual	SecondKPI	Jan	100	1	.	.
LeaderName	LeaderID	Actual	SecondKPI	Feb	100	2	.	.
LeaderName	LeaderID	Actual	SecondKPI	Mar	99.9	3	.	.
LeaderName	LeaderID	Actual	SecondKPI	Apr	100	4	.	.
LeaderName	LeaderID	Actual	SecondKPI	YTDActual	.	.	87.7	.
LeaderName	LeaderID	Goal	SecondKPI	Jan	100	1	.	.
LeaderName	LeaderID	Goal	SecondKPI	Feb	100	2	.	.
LeaderName	LeaderID	Goal	SecondKPI	Mar	100	3	.	.
LeaderName	LeaderID	Goal	SecondKPI	Apr	100	4	.	.
LeaderName	LeaderID	Goal	SecondKPI	May	100	5	.	.
LeaderName	LeaderID	Goal	SecondKPI	Jun	98.9	6	.	.
LeaderName	LeaderID	Goal	SecondKPI	Jul	99.7	7	.	.
LeaderName	LeaderID	Goal	SecondKPI	Aug	100	8	.	.
LeaderName	LeaderID	Goal	SecondKPI	Sep	100	9	.	.
LeaderName	LeaderID	Goal	SecondKPI	Oct	100	10	.	.
LeaderName	LeaderID	Goal	SecondKPI	Nov	100	11	.	.
LeaderName	LeaderID	Goal	SecondKPI	Dec	100	12	.	.
LeaderName	LeaderID	Goal	SecondKPI	YTDGoal	.	.	.	99.9
LeaderName	LeaderID	Actual	ThirdKPI	Jan	95.8	1	.	.
LeaderName	LeaderID	Actual	ThirdKPI	Feb	95.9	2	.	.
LeaderName	LeaderID	Actual	ThirdKPI	Mar	97.1	3	.	.
LeaderName	LeaderID	Actual	ThirdKPI	Apr	98.2	4	.	.
LeaderName	LeaderID	Actual	ThirdKPI	YTDActual	.	.	100	.
LeaderName	LeaderID	Goal	ThirdKPI	Jan	97.6	1	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Feb	98.1	2	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Mar	98.1	3	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Apr	97.1	4	.	.
LeaderName	LeaderID	Goal	ThirdKPI	May	97.1	5	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Jun	96.4	6	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Jul	96	7	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Aug	95.8	8	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Sep	95.9	9	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Oct	96.5	10	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Nov	97.5	11	.	.
LeaderName	LeaderID	Goal	ThirdKPI	Dec	96.2	12	.	.
LeaderName	LeaderID	Goal	ThirdKPI	YTDGoal	.	.	.	96.8
LeaderName	LeaderID	Actual	FourthKPI	Jan	99.4	1	.	.
LeaderName	LeaderID	Actual	FourthKPI	Feb	94.4	2	.	.
LeaderName	LeaderID	Actual	FourthKPI	Mar	83.9	3	.	.
LeaderName	LeaderID	Actual	FourthKPI	Apr	100	4	.	.
LeaderName	LeaderID	Actual	FourthKPI	YTDActual	.	.	96.7	.
LeaderName	LeaderID	Goal	FourthKPI	Jan	100	1	.	.
LeaderName	LeaderID	Goal	FourthKPI	Feb	100	2	.	.
LeaderName	LeaderID	Goal	FourthKPI	Mar	100	3	.	.
LeaderName	LeaderID	Goal	FourthKPI	Apr	100	4	.	.
LeaderName	LeaderID	Goal	FourthKPI	May	100	5	.	.
LeaderName	LeaderID	Goal	FourthKPI	Jun	100	6	.	.
LeaderName	LeaderID	Goal	FourthKPI	Jul	99.1	7	.	.
LeaderName	LeaderID	Goal	FourthKPI	Aug	99.1	8	.	.
LeaderName	LeaderID	Goal	FourthKPI	Sep	99.1	9	.	.
LeaderName	LeaderID	Goal	FourthKPI	Oct	98.8	10	.	.
LeaderName	LeaderID	Goal	FourthKPI	Nov	99.1	11	.	.
LeaderName	LeaderID	Goal	FourthKPI	Dec	100	12	.	.
LeaderName	LeaderID	Goal	FourthKPI	YTD	.	.	.	99.3

[DanH_sas]

Oops, missed one :-). The last observation should be YTDGoal.

[venkiparki_gmail_com]

Thanks much Dan!

This is exactly what I wanted with the bars.

But, I see more legends showing up when I specified   keylegend / ;

I am supposed to have only 4 legends (Actual, Goal, YTDActual, YTDGoal), but it is showing 6 legends with different colors. First 2 showing as line and the rest showing as square colored box. I want to get rid of those 2 square boxes that are not there and use the actual name as YTDActual and YTDGoal in the legend.

- Actual

- Goal

-Actual

-Goal

-Actual

-Goal

Do you know how to get only those 4 legends with the actual name?

Proc sgpanel data=Book4;

  panelby  KPI /  novarname;

  vline Month /group=Yr2014 response=Score lineattrs=(thickness=2) stat=mean;

  vbar Month / group=Yr2014 response=YTDActual  stat=mean;

  vbar Month /group=Yr2014 response=YTDGoal stat=mean;

  keylegend / ;

  rowaxis grid values=(60 to 100 by 10)  label='Score';

  colaxis grid DISCRETEORDER= DATA;

run;

[venkiparki_gmail_com]

I think I got it. If I change the Yr2014 column also to YTDActual and YTDGoal, the legend will automatically show only 2 with the actual names. I will try this out. Thanks for the insights!