BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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
1 ACCEPTED SOLUTION

Accepted Solutions
DanH_sas
SAS Super FREQ

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

4 REPLIES 4
DanH_sas
SAS Super FREQ

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
DanH_sas
SAS Super FREQ

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

venkiparki_gmail_com
Calcite | Level 5

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
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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