Data visualization with SAS programming

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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;

LeaderIDYr2014KPIMonthScoreMonthnumYTDActualYTDGoal
LeaderNameLeaderIDActualFirstKPIJan87.41..
LeaderNameLeaderIDActualFirstKPIFeb87.32..
LeaderNameLeaderIDActualFirstKPIMar88.33..
LeaderNameLeaderIDActualFirstKPIApr87.84..
LeaderNameLeaderIDGoalFirstKPIJan89.31..
LeaderNameLeaderIDGoalFirstKPIFeb88.42..
LeaderNameLeaderIDGoalFirstKPIMar88.93..
LeaderNameLeaderIDGoalFirstKPIApr89.64..
LeaderNameLeaderIDGoalFirstKPIMay905..
LeaderNameLeaderIDGoalFirstKPIJun88.76..
LeaderNameLeaderIDGoalFirstKPIJul877..
LeaderNameLeaderIDGoalFirstKPIAug89.48..
LeaderNameLeaderIDGoalFirstKPISep90.39..
LeaderNameLeaderIDGoalFirstKPIOct90.310..
LeaderNameLeaderIDGoalFirstKPINov91.211..
LeaderNameLeaderIDGoalFirstKPIDec89.312..
LeaderNameLeaderIDGoalFirstKPIYTD..87.789.4
LeaderNameLeaderIDActualSecondKPIJan1001..
LeaderNameLeaderIDActualSecondKPIFeb1002..
LeaderNameLeaderIDActualSecondKPIMar99.93..
LeaderNameLeaderIDActualSecondKPIApr1004..
LeaderNameLeaderIDGoalSecondKPIJan1001..
LeaderNameLeaderIDGoalSecondKPIFeb1002..
LeaderNameLeaderIDGoalSecondKPIMar1003..
LeaderNameLeaderIDGoalSecondKPIApr1004..
LeaderNameLeaderIDGoalSecondKPIMay1005..
LeaderNameLeaderIDGoalSecondKPIJun98.96..
LeaderNameLeaderIDGoalSecondKPIJul99.77..
LeaderNameLeaderIDGoalSecondKPIAug1008..
LeaderNameLeaderIDGoalSecondKPISep1009..
LeaderNameLeaderIDGoalSecondKPIOct10010..
LeaderNameLeaderIDGoalSecondKPINov10011..
LeaderNameLeaderIDGoalSecondKPIDec10012..
LeaderNameLeaderIDGoalSecondKPIYTD..10099.9
LeaderNameLeaderIDActualThirdKPIJan95.81..
LeaderNameLeaderIDActualThirdKPIFeb95.92..
LeaderNameLeaderIDActualThirdKPIMar97.13..
LeaderNameLeaderIDActualThirdKPIApr98.24..
LeaderNameLeaderIDGoalThirdKPIJan97.61..
LeaderNameLeaderIDGoalThirdKPIFeb98.12..
LeaderNameLeaderIDGoalThirdKPIMar98.13..
LeaderNameLeaderIDGoalThirdKPIApr97.14..
LeaderNameLeaderIDGoalThirdKPIMay97.15..
LeaderNameLeaderIDGoalThirdKPIJun96.46..
LeaderNameLeaderIDGoalThirdKPIJul967..
LeaderNameLeaderIDGoalThirdKPIAug95.88..
LeaderNameLeaderIDGoalThirdKPISep95.99..
LeaderNameLeaderIDGoalThirdKPIOct96.510..
LeaderNameLeaderIDGoalThirdKPINov97.511..
LeaderNameLeaderIDGoalThirdKPIDec96.212..
LeaderNameLeaderIDGoalThirdKPIYTD..96.796.8
LeaderNameLeaderIDActualFourthKPIJan99.41..
LeaderNameLeaderIDActualFourthKPIFeb94.42..
LeaderNameLeaderIDActualFourthKPIMar83.93..
LeaderNameLeaderIDActualFourthKPIApr1004..
LeaderNameLeaderIDGoalFourthKPIJan1001..
LeaderNameLeaderIDGoalFourthKPIFeb1002..
LeaderNameLeaderIDGoalFourthKPIMar1003..
LeaderNameLeaderIDGoalFourthKPIApr1004..
LeaderNameLeaderIDGoalFourthKPIMay1005..
LeaderNameLeaderIDGoalFourthKPIJun1006..
LeaderNameLeaderIDGoalFourthKPIJul99.17..
LeaderNameLeaderIDGoalFourthKPIAug99.18..
LeaderNameLeaderIDGoalFourthKPISep99.19..
LeaderNameLeaderIDGoalFourthKPIOct98.810..
LeaderNameLeaderIDGoalFourthKPINov99.111..
LeaderNameLeaderIDGoalFourthKPIDec10012..
LeaderNameLeaderIDGoalFourthKPIYTD..94.499.3

Report.JPG

Accepted Solutions
Solution
‎05-19-2014 01:33 PM
SAS Super FREQ
Posts: 925

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

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:

LeaderIDYr2014KPIMonthScoreMonthnumYTDActualYTDGoal
LeaderNameLeaderIDActualFirstKPIJan87.41..
LeaderNameLeaderIDActualFirstKPIFeb87.32..
LeaderNameLeaderIDActualFirstKPIMar88.33..
LeaderNameLeaderIDActualFirstKPIApr87.84..
LeaderNameLeaderIDGoalFirstKPIJan89.31..
LeaderNameLeaderIDGoalFirstKPIFeb88.42..
LeaderNameLeaderIDGoalFirstKPIMar88.93..
LeaderNameLeaderIDGoalFirstKPIApr89.64..
LeaderNameLeaderIDGoalFirstKPIMay905..
LeaderNameLeaderIDGoalFirstKPIJun88.76..
LeaderNameLeaderIDGoalFirstKPIJul877..
LeaderNameLeaderIDGoalFirstKPIAug89.48..
LeaderNameLeaderIDGoalFirstKPISep90.39..
LeaderNameLeaderIDGoalFirstKPIOct90.310..
LeaderNameLeaderIDGoalFirstKPINov91.211..
LeaderNameLeaderIDGoalFirstKPIDec89.312..
LeaderNameLeaderIDGoalFirstKPIYTDGoal...89.4
LeaderNameLeaderIDActualSecondKPIJan1001..
LeaderNameLeaderIDActualSecondKPIFeb1002..
LeaderNameLeaderIDActualSecondKPIMar99.93..
LeaderNameLeaderIDActualSecondKPIApr1004..
LeaderNameLeaderIDActualSecondKPIYTDActual..87.7.
LeaderNameLeaderIDGoalSecondKPIJan1001..
LeaderNameLeaderIDGoalSecondKPIFeb1002..
LeaderNameLeaderIDGoalSecondKPIMar1003..
LeaderNameLeaderIDGoalSecondKPIApr1004..
LeaderNameLeaderIDGoalSecondKPIMay1005..
LeaderNameLeaderIDGoalSecondKPIJun98.96..
LeaderNameLeaderIDGoalSecondKPIJul99.77..
LeaderNameLeaderIDGoalSecondKPIAug1008..
LeaderNameLeaderIDGoalSecondKPISep1009..
LeaderNameLeaderIDGoalSecondKPIOct10010..
LeaderNameLeaderIDGoalSecondKPINov10011..
LeaderNameLeaderIDGoalSecondKPIDec10012..
LeaderNameLeaderIDGoalSecondKPIYTDGoal...99.9
LeaderNameLeaderIDActualThirdKPIJan95.81..
LeaderNameLeaderIDActualThirdKPIFeb95.92..
LeaderNameLeaderIDActualThirdKPIMar97.13..
LeaderNameLeaderIDActualThirdKPIApr98.24..
LeaderNameLeaderIDActualThirdKPIYTDActual..100.
LeaderNameLeaderIDGoalThirdKPIJan97.61..
LeaderNameLeaderIDGoalThirdKPIFeb98.12..
LeaderNameLeaderIDGoalThirdKPIMar98.13..
LeaderNameLeaderIDGoalThirdKPIApr97.14..
LeaderNameLeaderIDGoalThirdKPIMay97.15..
LeaderNameLeaderIDGoalThirdKPIJun96.46..
LeaderNameLeaderIDGoalThirdKPIJul967..
LeaderNameLeaderIDGoalThirdKPIAug95.88..
LeaderNameLeaderIDGoalThirdKPISep95.99..
LeaderNameLeaderIDGoalThirdKPIOct96.510..
LeaderNameLeaderIDGoalThirdKPINov97.511..
LeaderNameLeaderIDGoalThirdKPIDec96.212..
LeaderNameLeaderIDGoalThirdKPIYTDGoal...96.8
LeaderNameLeaderIDActualFourthKPIJan99.41..
LeaderNameLeaderIDActualFourthKPIFeb94.42..
LeaderNameLeaderIDActualFourthKPIMar83.93..
LeaderNameLeaderIDActualFourthKPIApr1004..
LeaderNameLeaderIDActualFourthKPIYTDActual..96.7.
LeaderNameLeaderIDGoalFourthKPIJan1001..
LeaderNameLeaderIDGoalFourthKPIFeb1002..
LeaderNameLeaderIDGoalFourthKPIMar1003..
LeaderNameLeaderIDGoalFourthKPIApr1004..
LeaderNameLeaderIDGoalFourthKPIMay1005..
LeaderNameLeaderIDGoalFourthKPIJun1006..
LeaderNameLeaderIDGoalFourthKPIJul99.17..
LeaderNameLeaderIDGoalFourthKPIAug99.18..
LeaderNameLeaderIDGoalFourthKPISep99.19..
LeaderNameLeaderIDGoalFourthKPIOct98.810..
LeaderNameLeaderIDGoalFourthKPINov99.111..
LeaderNameLeaderIDGoalFourthKPIDec10012..
LeaderNameLeaderIDGoalFourthKPIYTD...99.3

View solution in original post


All Replies
Solution
‎05-19-2014 01:33 PM
SAS Super FREQ
Posts: 925

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

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:

LeaderIDYr2014KPIMonthScoreMonthnumYTDActualYTDGoal
LeaderNameLeaderIDActualFirstKPIJan87.41..
LeaderNameLeaderIDActualFirstKPIFeb87.32..
LeaderNameLeaderIDActualFirstKPIMar88.33..
LeaderNameLeaderIDActualFirstKPIApr87.84..
LeaderNameLeaderIDGoalFirstKPIJan89.31..
LeaderNameLeaderIDGoalFirstKPIFeb88.42..
LeaderNameLeaderIDGoalFirstKPIMar88.93..
LeaderNameLeaderIDGoalFirstKPIApr89.64..
LeaderNameLeaderIDGoalFirstKPIMay905..
LeaderNameLeaderIDGoalFirstKPIJun88.76..
LeaderNameLeaderIDGoalFirstKPIJul877..
LeaderNameLeaderIDGoalFirstKPIAug89.48..
LeaderNameLeaderIDGoalFirstKPISep90.39..
LeaderNameLeaderIDGoalFirstKPIOct90.310..
LeaderNameLeaderIDGoalFirstKPINov91.211..
LeaderNameLeaderIDGoalFirstKPIDec89.312..
LeaderNameLeaderIDGoalFirstKPIYTDGoal...89.4
LeaderNameLeaderIDActualSecondKPIJan1001..
LeaderNameLeaderIDActualSecondKPIFeb1002..
LeaderNameLeaderIDActualSecondKPIMar99.93..
LeaderNameLeaderIDActualSecondKPIApr1004..
LeaderNameLeaderIDActualSecondKPIYTDActual..87.7.
LeaderNameLeaderIDGoalSecondKPIJan1001..
LeaderNameLeaderIDGoalSecondKPIFeb1002..
LeaderNameLeaderIDGoalSecondKPIMar1003..
LeaderNameLeaderIDGoalSecondKPIApr1004..
LeaderNameLeaderIDGoalSecondKPIMay1005..
LeaderNameLeaderIDGoalSecondKPIJun98.96..
LeaderNameLeaderIDGoalSecondKPIJul99.77..
LeaderNameLeaderIDGoalSecondKPIAug1008..
LeaderNameLeaderIDGoalSecondKPISep1009..
LeaderNameLeaderIDGoalSecondKPIOct10010..
LeaderNameLeaderIDGoalSecondKPINov10011..
LeaderNameLeaderIDGoalSecondKPIDec10012..
LeaderNameLeaderIDGoalSecondKPIYTDGoal...99.9
LeaderNameLeaderIDActualThirdKPIJan95.81..
LeaderNameLeaderIDActualThirdKPIFeb95.92..
LeaderNameLeaderIDActualThirdKPIMar97.13..
LeaderNameLeaderIDActualThirdKPIApr98.24..
LeaderNameLeaderIDActualThirdKPIYTDActual..100.
LeaderNameLeaderIDGoalThirdKPIJan97.61..
LeaderNameLeaderIDGoalThirdKPIFeb98.12..
LeaderNameLeaderIDGoalThirdKPIMar98.13..
LeaderNameLeaderIDGoalThirdKPIApr97.14..
LeaderNameLeaderIDGoalThirdKPIMay97.15..
LeaderNameLeaderIDGoalThirdKPIJun96.46..
LeaderNameLeaderIDGoalThirdKPIJul967..
LeaderNameLeaderIDGoalThirdKPIAug95.88..
LeaderNameLeaderIDGoalThirdKPISep95.99..
LeaderNameLeaderIDGoalThirdKPIOct96.510..
LeaderNameLeaderIDGoalThirdKPINov97.511..
LeaderNameLeaderIDGoalThirdKPIDec96.212..
LeaderNameLeaderIDGoalThirdKPIYTDGoal...96.8
LeaderNameLeaderIDActualFourthKPIJan99.41..
LeaderNameLeaderIDActualFourthKPIFeb94.42..
LeaderNameLeaderIDActualFourthKPIMar83.93..
LeaderNameLeaderIDActualFourthKPIApr1004..
LeaderNameLeaderIDActualFourthKPIYTDActual..96.7.
LeaderNameLeaderIDGoalFourthKPIJan1001..
LeaderNameLeaderIDGoalFourthKPIFeb1002..
LeaderNameLeaderIDGoalFourthKPIMar1003..
LeaderNameLeaderIDGoalFourthKPIApr1004..
LeaderNameLeaderIDGoalFourthKPIMay1005..
LeaderNameLeaderIDGoalFourthKPIJun1006..
LeaderNameLeaderIDGoalFourthKPIJul99.17..
LeaderNameLeaderIDGoalFourthKPIAug99.18..
LeaderNameLeaderIDGoalFourthKPISep99.19..
LeaderNameLeaderIDGoalFourthKPIOct98.810..
LeaderNameLeaderIDGoalFourthKPINov99.111..
LeaderNameLeaderIDGoalFourthKPIDec10012..
LeaderNameLeaderIDGoalFourthKPIYTD...99.3
SAS Super FREQ
Posts: 925

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

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

New Contributor
Posts: 4

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

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;

New Contributor
Posts: 4

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

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!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 427 views
  • 0 likes
  • 2 in conversation