topic Re: SGPLOT: Displaying absolute and relative values on a bar chart in SAS Programming

SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Tue, 28 Feb 2023 14:15:53 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Tue, 28 Feb 2023 14:30:28 GMT

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

Bart

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Tue, 28 Feb 2023 14:32:05 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Tue, 28 Feb 2023 15:40:49 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Tue, 28 Feb 2023 16:17:39 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 10:29:21 GMT

@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;

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Wed, 01 Mar 2023 10:47:14 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 11:03:03 GMT

Thankyou very much

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 11:13:06 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Wed, 01 Mar 2023 11:29:55 GMT

I removed:

 datalabel seglabel

from the code.

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 12:10:44 GMT

@yabwon I added datalabel but it doesn't work

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Wed, 01 Mar 2023 12:18:25 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 12:23:57 GMT

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Wed, 01 Mar 2023 14:58:05 GMT

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

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 16:13:06 GMT

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:

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Wed, 01 Mar 2023 16:30:25 GMT

ok, now I don't understand what do you mean by "why does the data label not show anymore?" - there are data labels ( "XX (YY%)" ) on the plot, what other data label do you expect ?

Bart

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Wed, 01 Mar 2023 16:37:09 GMT

In sgplot there are datalabel and a seglabel statements, the seglabels label the segements, that is what we did. The datalabel labels the totals per bar, like bar 1 will have a total of 226 placed at the top of the bar. That is what I mean

Re: SGPLOT: Displaying absolute and relative values on a bar chart

yabwon — Wed, 01 Mar 2023 17:02:27 GMT

I think this should be it:

data have4;
set have3;
by period visit;
if first.visit then tot=0;
pos= tot + SumDosis/2;
tot + SumDosis;

if last.visit then /* this is to avoid ugly warnings in the log */ 
  do;
    total=tot;
    vst=visit;
  end;

length N_percentage $15 ;
N_Percentage=catx(" ", SumDosis, "(", put(percentage, percent8.2), ")");
keep period drug visit SumDosis N_percentage pos total vst;
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=vst response=total / datalabel=total seglabel
  OUTLINE OUTLINEATTRS=(color=black THICKNESS=1) NOFILL 
  ;
  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;

Bart

Re: SGPLOT: Displaying absolute and relative values on a bar chart

Anita_n — Thu, 02 Mar 2023 06:50:46 GMT

Thanks, it worked