Help with SGPLOT (need Bars displaying 0 Values)

[Ankit_ETL]

Hello,

i will try to explain my question in detail and as clearly as possible.

i have been tasked to create Horizontal bar graph for some user dashboard.

the graph will get updated whenever user selects some groups.

/*******example********/

/**********Test data ***********/
data test1;
input age_range $10. group;
label age_range = "Age Range";
cards;
0-30 Days 1
31-60 Days 2
61-90 Days 3
>91 Days  4
0-30 Days 2
31-60 Days 3
61-90 Days 4
>91 Days  3
0-30 Days 5
31-60 Days 5
61-90 Days 1
>91 Days 2
;
run;
/***********Test Data END**********/

data attrmap;
      input id $9. value $11. fillcolor $ @20;
   datalines;
Age_range 31-60 Days  cx007337
Age_range 0-30 Days   cx46A033 
Age_range 61-90 Days  cxED8800 
Age_range >91 Days   cxEC1C29  
;
run;

ods html body="c:\temp.html"  ;
ods listing close;
ods graphics / reset  width=8in height =3in;

proc sgplot data=test1  dattrmap=attrmap
nowall noborder noautolegend
;
  styleattrs  datacontrastcolors=(white);
  Hbar age_range / group=age_range attrid=Age_range
dataskin=gloss  barwidth=.5
filltype=solid
baselineattrs=(thickness=0)
datalabel datalabelattrs=(size=14);xaxis label="NUMBER OF CASES";
  run;
ods  text='<div align="center">My Current Open Cases</div>';
ods html close;

/********** example Code End*************/

All good till here .

now in the dashboard the user (lets say) selects group 4 and graph change to something like this

now this is the problem. Client expects the graph to display values for '0-30 days' and '31-60 days' also. Ofc a 0 will be displayed there but the problem is the way our stored procedure works, it filters all the data where group = 4 and creates a new data set that is used for sgplot.

i am not able to find a work around it.

any help is highly appreciated.

hope i am clear enough.

regards

Ankit

[Reeza]

There's a difference between a value of 0 and no value exists. The Test data does not have the 0 values in it, so you need to add them in somehow. 

The typical methods are PRELOADFMT or CLASSDATA - but either way, you need to know ahead of time which groups need to be present and the data needs to have the values you want to show. You have the data in the ATTRMAP so you can also use that as your CLASSDATA. And there's also a manual SQL join to create the data with the 0's.

---

@Ankit_ETL wrote:

Hello,

i will try to explain my question in detail and as clearly as possible.

i have been tasked to create Horizontal bar graph for some user dashboard.

the graph will get updated whenever user selects some groups.

/*******example********/

/**********Test data ***********/
data test1;
input age_range $10. group;
label age_range = "Age Range";
cards;
0-30 Days 1
31-60 Days 2
61-90 Days 3
>91 Days  4
0-30 Days 2
31-60 Days 3
61-90 Days 4
>91 Days  3
0-30 Days 5
31-60 Days 5
61-90 Days 1
>91 Days 2
;
run;
/***********Test Data END**********/

data attrmap;
      input id $9. value $11. fillcolor $ @20;
   datalines;
Age_range 31-60 Days  cx007337
Age_range 0-30 Days   cx46A033 
Age_range 61-90 Days  cxED8800 
Age_range >91 Days   cxEC1C29  
;
run;

ods html body="c:\temp.html"  ;
ods listing close;
ods graphics / reset  width=8in height =3in;

proc sgplot data=test1  dattrmap=attrmap
nowall noborder noautolegend
;
  styleattrs  datacontrastcolors=(white);
  Hbar age_range / group=age_range attrid=Age_range
dataskin=gloss  barwidth=.5
filltype=solid
baselineattrs=(thickness=0)
datalabel datalabelattrs=(size=14);xaxis label="NUMBER OF CASES";
  run;
ods  text='<div align="center">My Current Open Cases</div>';
ods html close;

/********** example Code End*************/

All good till here .

now in the dashboard the user (lets say) selects group 4 and graph change to something like this

now this is the problem. Client expects the graph to display values for '0-30 days' and '31-60 days' also. Ofc a 0 will be displayed there but the problem is the way our stored procedure works, it filters all the data where group = 4 and creates a new data set that is used for sgplot.

i am not able to find a work around it.

any help is highly appreciated.

hope i am clear enough.

regards

Ankit

---

[Ankit_ETL]

thanks for the reply.
is there any way i can change the value displayed at the end of the BAR. like can i give a manual formula to that. (count-1 or something like that.)

[Reeza]

I don't think you can apply a formula but I do think DATALABEL will take a variable value if you pre-calculated something.

DATALABEL <=variable>