How to get split graph with labels on the bars

[Ravindra_]

And also can you please let me know how do we populate the percentages on the graph for missing values on either side for every visit on each trt arm and the count of subjects at each visit on either side. I know its too much to ask you at this point, but if its not possible, i can tell the management that it may not be possible as i already sent this for review.

[Ksharp]

OK. Here is for your FULL graph . 

I think you owe me three hundred dollars.

/**************************
OK.Here is for your 13 Graphics.
Use "AVALC" to calculated percent instead of "TYPEAE".
***************************/

libname ana v9 'c:\temp';

proc format;
value visit
-0.5='Baseline'
1='Cycle 1'
2='Cycle 2'
3='Cycle 3'
4='Cycle 4'
5='Cycle 5'
6='Cycle 6'
7='Cycle 7'
8='Cycle 8'
9='Cycle 9'
10='Cycle 10'
11='Cycle 11'
12='Cycle 12'
13='Cycle 13'
14='Cycle 14'
15='Cycle 15'
16='Cycle 16'
;
value avalc
/*.='Missing'*/
12='NEVER'
11='MILD'
10='MODERTATE' 
9='SEVERE'
8='VERY SEVERE' 
7='QUITE A BIT' 
6='A LITTLE BIT'
5='SOMEWHAT' 
4='RARELY' 
3='OCCASIONALLY'
2='FREQUENTLY' 
1='ALMOST CONSTANTLY' 
0='VERY MUCH' 
;
run;
%macro plotit(dsn=,PARAML=,TYPEAE=);
data have;   
 set &dsn.;
if PARAML="&PARAML." and TYPEAE="&TYPEAE.";
keep USUBJID AVALC VISLBL TRTPN;
run;
data have;
 set have;
if AVALC='NONE' then aval_cal=12;
if AVALC='NEVER' then aval_cal=12;
if AVALC='NOT AT ALL' then aval_cal=12;
if AVALC='MILD' then aval_cal=11;
if AVALC='MODERTATE' then aval_cal=10;
if AVALC='SEVERE' then aval_cal=9;
if AVALC='VERY SEVERE' then aval_cal=8;
if AVALC='QUITE A BIT' then aval_cal=7;
if AVALC='A LITTLE BIT' then aval_cal=6;
if AVALC='SOMEWHAT' then aval_cal=5;
if AVALC='RARELY' then aval_cal=4;
if AVALC='OCCASIONALLY' then aval_cal=3;
if AVALC='FREQUENTLY' then aval_cal=2;
if AVALC='ALMOST CONSTANTLY' then aval_cal=1;
if AVALC='VERY MUCH' then aval_cal=0;

if vislbl='Baseline' then visit=-0.5;
if vislbl='Cycle 2' then visit=1;
if vislbl='Cycle 3' then visit=2;
if vislbl='Cycle 4' then visit=3;
if vislbl='Cycle 5' then visit=4;
if vislbl='Cycle 6' then visit=5;
if vislbl='Cycle 7' then visit=6;
if vislbl='Cycle 8' then visit=7;
if vislbl='Cycle 9' then visit=8;
if vislbl='Cycle 10' then visit=9;
if vislbl='Cycle 11' then visit=10;
if vislbl='Cycle 12' then visit=11;
if vislbl='Cycle 13' then visit=12;
if vislbl='Cycle 14' then visit=13;
if vislbl='Cycle 15' then visit=14;
if vislbl='Cycle 16' then visit=15;
run;
proc sql;
create table missing_percent as
select TRTPN,visit,
count(*)/(select count(*) from have where  TRTPN=a.TRTPN and visit=a.visit) as miss_pct  
 from have  as a
 where aval_cal is  missing  
  group by TRTPN,visit
   order by 2,1;

create table n_subjid as
select TRTPN,visit,count(distinct  USUBJID) as n
 from have  as a
  group by TRTPN,visit
   order by 2,1;


create table have2 as
select TRTPN,visit,aval_cal,
count(*)/(select count(*) from have where  TRTPN=a.TRTPN and visit=a.visit and aval_cal is not missing) as pct  /* aval_cal is not missing*/
 from have  as a
 where aval_cal is not missing  /* aval_cal is not missing*/
  group by TRTPN,visit,aval_cal ;

quit;

data have3;
 set have2;
 by TRTPN visit ;
 if first.visit then cum=0;
 cum+pct;
 run;
data have4;
 set have3;
 if TRTPN=1 then cum=-cum;  /*left side data*/
run;
data have5;
 set have4;
 by TRTPN visit;
 lag_cum=lag(cum);
 if first.visit then lag_cum=0;
 drop pct;
run;
data  have6;
 set have5;
 if TRTPN=1 then do;high=lag_cum;low=cum;end;
 else do;high=cum;low=lag_cum;end;
 drop cum lag_cum;
run;



proc transpose data=missing_percent out=missing_percent2(drop=_:) prefix=p;
by visit;
var miss_pct;
id TRTPN;
run;
proc transpose data=n_subjid out=n_subjid2(drop=_:) prefix=n;
by visit;
var n;
id TRTPN;
run;




data have7;
if _n_=1 then do;
 if 0 then set missing_percent2;
 declare hash h1(dataset:'missing_percent2');
 h1.definekey('visit');
 h1.definedata('p1','p2');
 h1.definedone();
 if 0 then set n_subjid2;
 declare hash h2(dataset:'n_subjid2');
 h2.definekey('visit');
 h2.definedata('n1','n2');
 h2.definedone();
end;
 set have6;
 by TRTPN visit;
call missing(p1,p2,n1,n2);
if TRTPN=1 and  first.visit then do;
rc=h1.find();
rc=h2.find();
p1_p=-0.9;
p2_p=0.9;
zero=0;
text=put(visit,visit. -l);
end;
format  visit visit. aval_cal avalc. ;
if high<=0 then do;high=high-0.1;low=low-0.1;end;
if low>=0 then do;high=high+0.1;low=low+0.1;end;
run;
proc sort data=have7;by TRTPN visit descending aval_cal;run;
proc sgplot data=have7 noborder;
title "PARAML=&PARAML.  TYPEAE=&TYPEAE. ";
title2  'Tagrisson 80 mg                                    Chemotherapy ';
styleattrs datacolors=( verylightgreen navy  blue  yellow orange    lightblue green lightred red) ;
highlow y=visit low=low high=high/type=bar group=aval_cal grouporder=data nooutline name='a';
scatter y=visit x=p1_p/markerchar=p1 markercharattrs=(size=8) x2axis;
scatter y=visit x=p2_p/markerchar=p2 markercharattrs=(size=8) x2axis;
text y=visit x=zero text=text/strip contributeoffsets=none /*splitchar=' ' splitpolicy=splitalways*/ textattrs=(size=8);
yaxistable n1/y=visit location=outside position=left valueattrs=(size=8 ) VALUEJUSTIFY=right VALUEHALIGN=right ;
yaxistable n2/y=visit location=outside position=right valueattrs=(size=8) VALUEJUSTIFY=left VALUEHALIGN=left;
xaxis values=(-1.1 to 1.1 by 0.2) display=(nolabel noline) valuesdisplay=(
'100%' '80%' '60%' '40%' '20%' '0%'  '0%' '20%' '40%' '60%' '80%' '100%') offsetmin=0 offsetmax=0 ;
x2axis values=(-1 -0.7 0 0.7 1) display=(nolabel noline noticks) valuesdisplay=(
' '  'Patients Without Symp(%)' ' '  'Patients Without Symp(%)'  ' ') offsetmin=0 offsetmax=0 ;
yaxis reverse display=none ;
keylegend 'a' /title='Frequency:' ;
label n1='N' n2='N' p1='Patients Without Symp(%)' p2='Patients Without Symp(%)';
format p1 p2 percent8.0;
run;
%mend;


proc freq data=ANA.ADQS4 noprint;
table PARAML*TYPEAE/out=levels list;
run;
data _null_;
 set levels;
call execute( catt('%plotit(dsn=ANA.ADQS4,PARAML=',PARAML,',TYPEAE=',TYPEAE,')')  );
run;

[Ravindra_]

Thanks a lot Ksharp, i think i owe you even more than that for providing me such a beautiful output. Please come down to London and we can have a big party and also i am basically a Salesforce developer with additional basic SAS skills, i can share all my Salesforce knowledge for free. Thanks a lot for the help.

[Ksharp]

Good. Hope one day I could come to London and see the Big Ben .