* shift_tables.sas;

libname derlib "c:\shift";

%global n1 n2;

data adsl1;
 set derlib.adsl;
 where saffl='Y';
run;

%macro pop();
 %do i=1 %to 2;
 proc sql noprint;
 select count(distinct usubjid) into :n&i
 from adsl1
 where trt01pn=&i;
 quit;
%end;
%mend pop;

%pop;
%put &n1;
%put &n2; 

data lb1(keep=usubjid param paramcd atptn atpt visit visitnum avalct1n
 avalcat1 basecat1 basect1n trt01pn);
 set derlib. adlb;
 where saffl='Y' and anlfl='Y' and lbcat='HEMATOLOGY' and paramcd='HGB'
 and 4 le atptn and not missing(avalct1n) and not missing(basect1n) ;
run;

proc format;
 value avalc
 1="LOW"
 2="NORMAL"
 3="HIGH"
 4="TOTAL" ;
 value atpt
 3
 4="Post-dose";
run;

proc sort data=lb1;
 by usubjid param atptn;
run;

data lb1;
 set lb1;
 output;
 avalct1n=4;
 output;
run;

data lb1;
 set lb1;
 output;
 basect1n=4;
 output;
run; 

proc freq data=lb1 noprint;
 tables trt01pn*param*atptn*avalct1n*basect1n /
 out=count(drop=percent where=(not missing(avalct1n) and not
 missing(basect1n)));
run;

proc sort data=lb1 out=dummy(keep=param) nodupkey;
 by param;
run;

data dummy1;
 set dummy;
 by param;
 
do trt01pn=1 to 2;
  do atptn=4 to 4;
   do avalct1n=1 to 4;
    do basect1n=1 to 4;
     output;
    end;
   end;
  end;
 end;
run; 

proc sort data=count;
 by param trt01pn atptn avalct1n basect1n;
run;

proc sort data=dummy1;
 by param trt01pn atptn avalct1n basect1n;
run;

data combined;
 merge dummy1 count;
 by param trt01pn atptn avalct1n basect1n;
run;

data combined;
 set combined;
 by param trt01pn atptn avalct1n basect1n;
 if trt01pn=2 and basect1n=1 then basect1n=5;
 else if trt01pn=2 and basect1n=2 then basect1n=6;
 else if trt01pn=2 and basect1n=3 then basect1n=7;
 else if trt01pn=2 and basect1n=4 then basect1n=8;
run;

proc sort data=combined;
 by param atptn avalct1n basect1n;
run;

proc transpose data=combined out=final(drop=_name_ _label_) prefix=col;
 by param atptn avalct1n;
 var count;
 id basect1n;
run;

data denomi;
 set final;
 by param;
 where avalct1n=4;
 retain rownum;
if first.param then rownum+1;
run;

%global l1 r1 ;

Data _null_;
 Set denomi;
 if n(col4)=1 then
 Call symput('l'||trim(left(rownum)),trim(left(col4)));
 else Call symput('l'||trim(left(rownum)),0);
 if n(col8)=1 then
 Call symput('r'||trim(left(rownum)),trim(left(col8)));
 else Call symput('r'||trim(left(rownum)),0);
run; 

data final1(drop=col1 col2 col3 col4 col5 col6 col7 col8 i);
 length tot1 tot2 tot3 tot4 tot5 tot6 tot7 tot8 $15;
 set final;
 array treat{8} col1 col2 col3 col4 col5 col6 col7 col8;
 array tot{8} tot1 tot2 tot3 tot4 tot5 tot6 tot7 tot8;
 do i=1 to 8;
 if i <= 4 then do;
 if treat{i} ne . then tot{i}=trim(left(put(treat{i},best.))) || " (" ||
 trim(left(put((treat{i}/&L1)*100,5.1))) || "%)"; end;
 else if i > 4 then do;
 if treat{i} ne . then tot{i}=trim(left(put(treat{i},best.))) || " (" ||
 trim(left(put((treat{i}/&r1)*100,5.1))) || "%)"; end;
 if treat{i} eq . and (&l1 ne 0 or &r1 ne 0) then tot{i}="0 (0.0%) ";
 end;
run;

data final1;
 set final1;
 length atpt $50 avalcat1 $15;
 atpt=strip(put(atptn,atpt.));
 avalcat1=strip(put(avalct1n,avalc.));
run;

data final2;
 length avalcat1 $20;
 set dummy
 final1;
 by param;
 if first.param then avalcat1=param;
 avalcat1=propcase(avalcat1);
run; 

ods rtf file = "c:\shift\shift.rtf" bodytitle;

proc report data=final2 missing center nowd headline headskip split='|'

 style(report)={outputwidth=9 in}
 style(header)={just=center}/*{protectspecialchars=off}*/;

 column param atptn avalcat1
 ("Treatment 1|(N=%trim(&n1))|Baseline|" tot1 tot2 tot3 tot4 )
 ("Treatment 2|(N=%trim(&n2))|Baseline|" tot5 tot6 tot7 tot8);

 define param / noprint order ;
 define atptn / order noprint ;
 define avalcat1 / display "Parameter|Post Dose" style=[just=left
 cellwidth=0.75in asis=on];

 define tot1 /display "Low" style(column)=[just=center cellwidth=0.5in];
 define tot2 /display "Normal" style(column)=[just=center cellwidth=0.5in];
 define tot3 /display "High" style(column)=[just=center cellwidth= 0.5in];
 define tot4 /display "Total" style(column)=[just=center cellwidth= 0.5in];
 define tot5 /display "Low" style(column)=[just=center cellwidth=0.5in];
 define tot6 /display "Normal" style(column)=[just=center cellwidth=0.5in];
 define tot7 /display "High" style(column)=[just=center cellwidth= 0.5in];
 define tot8 /display "Total" style(column)=[just=center cellwidth= 0.5in];

 compute before atptn / style=[just=center cellwidth= 0.5in];
 line " ";
 endcomp;

 break after param/page;
 title 'Shift from Baseline in Hemoglobin By Treatment';
 title2 'Safety Population';
run;

ods rtf close;