SGPLOT: Displaying absolute and relative values on a bar chart

[Anita_n]

Dear all,

assuming I have a a bar chart plotted using sgplot and I wish to add behind the absolute values the relative values in bracket. Is this possible? If yes please any help. I created a label with both values and assigned this as follows seglabel=mylabel but this doesnt work

[yabwon]

"absolute values the relative values in bracket." - relative to what? Do you want to have something like: "10 (30%)" ?

Bart

[Anita_n]

Yes,  that is what I mean. The percentages in bracket

[yabwon]

something like this:

data have;
input year val group $;
cards;
2012 2 g
2012 6 r
2012 1 b
2013 7 r
2013 1 b
2014 2 g
2015 3 r
2016 1 g
2016 4 r
2017 2 g
2017 5 r
2017 1 b
;
run;

data have2;

  total = 0;
  do _N_ = 1 by 1 until(last.year);
    set have;
    by year;
    total + val;     
  end;

  tot = 0;
  do _N_ = 1 to _N_;
    set have;
    pos = tot + 0.5*val;
    tot + val;
    length vallabel $ 20;
    vallabel = cats(val,"(", put(val/total, percent10.2 ),")");
    output;
  end;

run;

proc print;
run;

proc sgplot data = have2;
  STYLEATTRS DATACOLORS=(lightgreen lightred lightblue);

  VBARPARM  category=year response=val / group=group 
   GROUPORDER=DATA 
   GROUPDISPLAY=STACK
  ;
  text x=year y=pos text=vallabel / group=group
   TEXTATTRS=(Color=black Family="Arial" Size=8 Weight=bold)
  ;
run;

?

bart

[Anita_n]

Thankyou for that, very interesting to know that there is a possibility like this. Am still tring it. Haven't got it right with the positioning (pos). I will let you know if it worked

[Anita_n]

@yabwon since am still not getting the positioning let me post how my data really looks like maybe you can help

data have;                                          
infile datalines;                                   
input period $10.  drug $15.  visit dosis 2.;
datalines; 
2000-2005	Cefalexin       1 12     
2000-2005	Calcipotriol	1 16     
2000-2005	Folic acid	    1 26     
2000-2005	Piriton         1 3      
2000-2005	Fentanyl	    1 36     
2000-2005	Folic acid      1 26     
2000-2005	Co-beneldopa	1 35     
2000-2005	Folic acid      1 26     
2000-2005	Cefalexin	    1 11     
2000-2005	Fentanyl	    1 35     
2006-2011	Allopurinol     1 33     
2006-2011	Folic acid      1 27     
2006-2011	Cefalexin       1 11     
2006-2011	Allopurinol	    1 33     
2006-2011	Cefalexin       1 11     
2006-2011	Folic acid      1 26     
2006-2011	Piriton	        1 3      
2006-2011	Folic acid	    1 27     
2006-2011	Allopurinol	    1 33     
2006-2011	Piriton	        1 3      
2006-2011	Cefalexin       1 11     
2011-2022	Piriton	        1 3      
2011-2022	Cefalexin	    1 11     
2011-2022	Cefalexin	    1 12     
2011-2022	Cefalexin	    1 11     
2011-2022	Cefalexin	    1 11     
2011-2022	Baclofen	    1 35     
2011-2022	Diclofenac	    1 22     
2011-2022	Diclofenac      1 22     
2011-2022	Co-beneldopa	1 35     
2011-2022	Co-beneldopa	1 3
2011-2022   Other Piriton   1 22 
2000-2005	Co-beneldopa    2 3      
2000-2005	Folic acid      2 15     
2000-2005	Cefalexin		2 14     
2000-2005	Fentanyl	    2 11     
2000-2005	Allopurinol     2 3      
2000-2005	Folic acid      2 3      
2000-2005	Cefalexin       2 5      
2000-2005	Allopurinol	    2 15     
2000-2005	Cefalexin       2 35     
2006-2011	Folic acid  	2 16     
2006-2011	Piriton	        2 22     
2006-2011	Folic acid	    2 38     
2006-2011	Allopurinol	    2 11     
2006-2011	Piriton	        2 7      
2006-2011	Cefalexin       2 11     
2006-2011	Piriton	        2 11     
2006-2011	Cefalexin	    2 15     
2006-2011	Cefalexin	  	2 17     
2006-2011	Cefalexin	    2 11     
2011-2022	Cefalexin	    2 33     
2011-2022	Baclofen	    2 77     
2011-2022	Diclofenac	    2 25     
2011-2022	Acrivastine     2 12     
2011-2022	Acrivastine     2 8     
2011-2022	Acrivastine     2 15     
2011-2022	Acrivastine     2 20     
2011-2022	Acrivastine     2 6     
2011-2022	Acrivastine     2 20     
2011-2022	Acrivastine     2 33
2011-2022   Other Piriton   2 14
2000-2005	Cefalexin       3 14     
2000-2005	Calcipotriol	3 18     
2000-2005	Folic acid	    3 16     
2000-2005	Piriton         3 15     
2000-2005	Fentanyl	    3 19     
2000-2005	Folic acid      3 20     
2000-2005	Co-beneldopa  3 25     
2000-2005	Folic acid    3 52     
2000-2005	Cefalexin	  3 25     
2000-2005	Fentanyl	  3 16     
2006-2011	Allopurinol   3 8       
2006-2011	Folic acid    3 2       
2006-2011	Cefalexin     3 55       
2006-2011	Allopurinol	  3 70       
2006-2011	Cefalexin     3 18       
2006-2011	Folic acid    3 1       
2006-2011	Piriton	      3 20       
2006-2011	Folic acid	  3 11       
2006-2011	Allopurinol	  3 14       
2006-2011	Piriton	      3 7      
2006-2011	Cefalexin     3 33       
2011-2022	Piriton	      3 20       
2011-2022	Cefalexin	  3 15       
2011-2022	Cefalexin	  3 12       
2011-2022	Cefalexin	  3 77       
2011-2022	Cefalexin	  3 6       
2011-2022	Acrivastine	  3 44       
2011-2022	Acrivastine	  3 7       
2011-2022	Acrivastine	  3 25       
2011-2022	Acrivastine	  3 11       
2011-2022	Acrivastine	  3 9   
2011-2022   Other Piriton 3 8
;                                  
run;                               

proc sql;
create table have2 as select period, drug, visit,  sum(dosis) as SumDosis from have group by period, drug, visit;

create table sumtotal as select period,visit, sum(SumDosis) as totals  from have2  group by period, visit;

create table have3 as select a.*, b.totals, SumDosis/totals as percentage format percent8.2 from have2 as 
a, sumtotal as b where a.visit=b.visit and a.period=b.period;

quit;

data have4;
set have3;
length N_percentage $15 ;
N_Percentage=catx(" ", SumDosis, "(", put(percentage, percent8.2), ")");
keep period drug visit SumDosis N_percentage;
run;
proc sort data have4;
by period visit drug;
run;

proc sgpanel data = have4;
styleattrs datacolors=(lightgreen lightred lightblue);
panelby period /columns=3 novarname noborder;
 vbarparm  category=visit response=SumDosis / group=drug grouporder=data  datalabel seglabel groupdisplay=stack;
  *text x=year y=pos text=vallabel / group=group TEXTATTRS=(Color=black Family="Arial" Size=8 Weight=bold)
  ;
run;

 This is how it looks like when I apply your code:

data have5;
tot = 0;
  do _N_ = 1 to _N_;
    set have4;
    pos = tot + 0.5*SumDosis;
    tot + SumDosis;
      glabel = N_percentage;
    output;
  end;

run;

proc sgpanel data = have5;
styleattrs datacolors=(lightgreen lightred lightblue);
panelby period /columns=3 novarname noborder;
 vbarparm  category=visit response=SumDosis / group=drug grouporder=data  datalabel  groupdisplay=stack;
  text x=visit y=pos text=glabel / group=drug TEXTATTRS=(Color=black Family="Arial" Size=8 Weight=bold)
  ;
run;

[yabwon · Accepted solution]

try this:

data have;                                          
infile datalines dlm="|";                                   
input period : $ 10.  drug : $ 15.  visit dosis 2.;
datalines; 
2000-2005|Cefalexin     |1|12
2000-2005|Calcipotriol  |1|16
2000-2005|Folic acid    |1|26
2000-2005|Piriton       |1|3 
2000-2005|Fentanyl      |1|36
2000-2005|Folic acid    |1|26
2000-2005|Co-beneldopa  |1|35
2000-2005|Folic acid    |1|26
2000-2005|Cefalexin     |1|11
2000-2005|Fentanyl      |1|35
2006-2011|Allopurinol   |1|33
2006-2011|Folic acid    |1|27
2006-2011|Cefalexin     |1|11
2006-2011|Allopurinol   |1|33
2006-2011|Cefalexin     |1|11
2006-2011|Folic acid    |1|26
2006-2011|Piriton       |1|3 
2006-2011|Folic acid    |1|27
2006-2011|Allopurinol   |1|33
2006-2011|Piriton       |1|3 
2006-2011|Cefalexin     |1|11
2011-2022|Piriton       |1|3 
2011-2022|Cefalexin     |1|11
2011-2022|Cefalexin     |1|12
2011-2022|Cefalexin     |1|11
2011-2022|Cefalexin     |1|11
2011-2022|Baclofen      |1|35
2011-2022|Diclofenac    |1|22
2011-2022|Diclofenac    |1|22
2011-2022|Co-beneldopa  |1|35
2011-2022|Co-beneldopa  |1|3
2011-2022|Other Piriton |1|22
2000-2005|Co-beneldopa  |2|3 
2000-2005|Folic acid    |2|15
2000-2005|Cefalexin     |2|14
2000-2005|Fentanyl      |2|11
2000-2005|Allopurinol   |2|3 
2000-2005|Folic acid    |2|3 
2000-2005|Cefalexin     |2|5 
2000-2005|Allopurinol   |2|15
2000-2005|Cefalexin     |2|35
2006-2011|Folic acid    |2|16
2006-2011|Piriton       |2|22
2006-2011|Folic acid    |2|38
2006-2011|Allopurinol   |2|11
2006-2011|Piriton       |2|7 
2006-2011|Cefalexin     |2|11
2006-2011|Piriton       |2|11
2006-2011|Cefalexin     |2|15
2006-2011|Cefalexin     |2|17
2006-2011|Cefalexin     |2|11
2011-2022|Cefalexin     |2|33
2011-2022|Baclofen      |2|77
2011-2022|Diclofenac    |2|25
2011-2022|Acrivastine   |2|12
2011-2022|Acrivastine   |2|8 
2011-2022|Acrivastine   |2|15
2011-2022|Acrivastine   |2|20
2011-2022|Acrivastine   |2|6 
2011-2022|Acrivastine   |2|20
2011-2022|Acrivastine   |2|33
2011-2022|Other Piriton |2|14
2000-2005|Cefalexin     |3|14
2000-2005|Calcipotriol  |3|18
2000-2005|Folic acid    |3|16
2000-2005|Piriton       |3|15
2000-2005|Fentanyl      |3|19
2000-2005|Folic acid    |3|20
2000-2005|Co-beneldopa  |3|25
2000-2005|Folic acid    |3|52
2000-2005|Cefalexin     |3|25
2000-2005|Fentanyl      |3|16
2006-2011|Allopurinol   |3|8 
2006-2011|Folic acid    |3|2 
2006-2011|Cefalexin     |3|55
2006-2011|Allopurinol   |3|70
2006-2011|Cefalexin     |3|18
2006-2011|Folic acid    |3|1 
2006-2011|Piriton       |3|20
2006-2011|Folic acid    |3|11
2006-2011|Allopurinol   |3|14
2006-2011|Piriton       |3|7 
2006-2011|Cefalexin     |3|33
2011-2022|Piriton       |3|20
2011-2022|Cefalexin     |3|15
2011-2022|Cefalexin     |3|12
2011-2022|Cefalexin     |3|77
2011-2022|Cefalexin     |3|6 
2011-2022|Acrivastine   |3|44
2011-2022|Acrivastine   |3|7 
2011-2022|Acrivastine   |3|25
2011-2022|Acrivastine   |3|11
2011-2022|Acrivastine   |3|9 
2011-2022|Other Piriton |3|8
;                                  
run;  
                             
proc print;
run;

proc sql;
create table have2 as select period, drug, visit,  sum(dosis) as SumDosis from have group by period, drug, visit;

create table sumtotal as select period,visit, sum(SumDosis) as totals  from have2  group by period, visit;

create table have3 as select a.*, b.totals, SumDosis/totals as percentage format percent8.2 from have2 as 
a, sumtotal as b where a.visit=b.visit and a.period=b.period;

quit;

proc print;
run;


proc sort data=have3;
by period visit drug;
run;

data have4;
set have3;
by period visit;
if first.visit then tot=0;
pos= tot + SumDosis/2;
tot + SumDosis;
length N_percentage $15 ;
N_Percentage=catx(" ", SumDosis, "(", put(percentage, percent8.2), ")");
keep period drug visit SumDosis N_percentage pos;
run;

proc print;
run;


ods graphics / width=1200px height=1000px;
proc sgpanel data = have4;

  styleattrs datacolors=(
CXfc8d59
CXfdbb84
CXfdd49e
CXfee8c8
CXfff7ec
CXffffff
CXfff7fb
CXece7f2
CXd0d1e6
CXa6bddb
CX74a9cf
);
  panelby period / columns=3 novarname noborder;
  vbarparm  category=visit response=SumDosis / group=drug grouporder=data  groupdisplay=stack
  DATASKIN=NONE OUTLINE OUTLINEATTRS=(color=black THICKNESS=1) FILL 
  ;
  text x=visit y=pos text=N_Percentage / group=drug TEXTATTRS=(Color=black Family="Arial" Size=6 Weight=bold);
run;

should give you:

Bart

[Anita_n]

Thankyou very much

[Anita_n]

@yabwon why does the datalabel not show anymore? Does it have any thing to do with the TEXT-Statement?

[yabwon]

I removed: 

 datalabel seglabel

from the code.

B.

[Anita_n]

@yabwon  I added datalabel but it doesn't work

[yabwon]

Could you put a print screen here, and also the log from the proc sgplot?

[Anita_n]

[yabwon]

Screen shot of the _graph_, and _text_ of the log.

B.

[Anita_n]

Here is the code:

data have4;
set have3;
by period visit;
if first.visit then tot=0;
pos= tot + SumDosis/2;
tot + SumDosis;
length N_percentage $15 ;
N_Percentage=catx(" ", SumDosis, "(", put(percentage, percent8.2), ")");
keep period drug visit SumDosis N_percentage pos;
run;


ods graphics / width=1200px height=1000px;
proc sgpanel data = have4 noautolegend;
styleattrs datacolors=(lightgreen lightred lightblue);
panelby period /columns=3 novarname noborder;
vbarparm  category=visit response=SumDosis / group=drug  grouporder=data datalabel datalabelattrs=(color=black family="Arial" size=8 weight=bold) groupdisplay=stack name='a' ;
text x=visit y=pos text=n_percentage / group=drug TEXTATTRS=(Color=black Family="Arial" Size=8) name='b';
rowaxis values=(0 to 300 by 50);
keylegend 'a'/ position=bottom across=6 title=" ";
run;

The graph:

and log: