Laboratory Data Processing

            *************************************************************;
            ** Name:    lab4.sas                                       **;
            ** Author:  T.V. Ramesh                                    **;
            ** Desc:    Program to read lab data from oracle tables    **;
            **          and create reports that will be run weekly     **;
            ** Notes:   - Integrate tables and graphs with data        **;
            **            extraction so that same info is used in both **;
            **          - Modularize program to be run for any lab var **;
            **          - Make program independent of actevent and     **;
            **            dosage group so it can run through out study **;
            **          - Use Proc Report instead of data _null_ for   **;
            **            quick turnaround in standard report format   **;
            **          - Make program adaptable for other studies     **;
            *************************************************************;
            %include 'ro3201195:[rb16074.interim1.adhoc]formats.sas';
            %include 'ro3201195:[rb16074.interim1.adhoc]tf.sas';
            
            options nomacrogen nosymbolgen nomlogic nomprint;
            %let study=RB16074;
            %let compound=RO3201195;
            %let rotate=landscape;  ** portrait,landscape            **;
            %let device=cgmof97l;   ** xcolor,xbw,xgray,win,cgmof97L **;
            %let target=cgmof97l;   ** lj5sips,clj                   **;
            %let gsfname=grafout;   ** grafout or blank              **;
            %let blind=N;
            %let env=;        ** leave blank for prod          **;
            
            ** to reference appropriate CGM device driver, need to call it gdevice0 **;
            libname gdevice0 "&compound:[&study..interim1.data]";
            
            ** to reference randmomization dataset. used separate **;
            ** libref than above for cleaner standards            **;
            libname dat16074 "&compound:[&study..interim1.data]";
            
            ** to suit proc report output **;
            options ls=132 ps=50 formchar='|_---|+|---+=|-/\<>*' nodate center;
            
            ** fileref for gsfname is assigned before graph procedure  **;
            goptions rotate=&rotate device=&device target=&target colors=(black)
                               gsfname=&gsfname gsfmode=replace gsflen=8092;
            
            ***********************************************************;
            ** Assign defaults for plots and charts. Using a common  **;
            ** section cuz making changes would be easier later on   **;
            ***********************************************************;
              ** use this for plotting actual data **;
              axis1      value=(h=.75
                           tick=1 j=c ' '
                           tick=2 j=c 'Screening'
                           tick=3 j=c 'Day 1'
                           tick=4 j=c 'Day 7'
                           tick=5 j=c 'Day 14'
                           tick=6 j=c 'Day 21'
                           tick=7 j=c 'Day 28'
                           tick=8 j=c 'Day 33'
                           tick=9 j=c '')
                        label=(j=center h=.75 "SCHEDULED TIME") major=none minor=none;
            
              ** use this for plotting change variables **;
              axis2    value=(h=.75
                           tick=1 j=c ' '
                           tick=2 j=c 'Day 7'
                           tick=3 j=c 'Day 14'
                           tick=4 j=c 'Day 21'
                           tick=5 j=c 'Day 28'
                           tick=6 j=c 'Day 33'
                           tick=7 j=c ' ')
                    label=(j=center h=.75  "SCHEDULED TIME") major=none minor=none;
            
              ** axis3 (vert axis) and legend are same for scatter and hilo **;
              axis3     value=(h=.75) minor=none label=none;
            
              legend across=8
                       value=(h=.6)
                       shape=symbol(3,.5)
                       label=none
                       origin=(0 in,0.25 in);
            
              ** using commong legends for scatter and hilo for consistency **;
              %macro sym(i,h);
                symbol1 i=&i h=&h  f=marker  v="W" ; ** filled circle   **;
                symbol2 i=&i h=&h  f=swissxb v="X" ; ** bold X          **;
                symbol3 i=&i h=&h  f=markere v="C" ; ** open triangle   **;
                symbol4 i=&i h=&h  f=markere v="P" ; ** open diamond    **;
                symbol5 i=&i h=&h  f=markere v="W" ; ** open circle     **;
                symbol6 i=&i h=&h  f=marker  v="C" ; ** filled triangle **;
                symbol7 i=&i h=&h  f=marker  v="P" ; ** filled diamond  **;
                symbol8 i=&i h=&h  f=markere v="U" ; ** open square     **;
              %mend sym;
            
            options nomacrogen nosymbolgen  nomlogic  nomprint;
            
            ****************************************************************;
            ** Run this macro to extract appropriate records from Normlab **;
            ** containing hi range flag and units. This data is not there **;
            ** in the views corresponding to DCMs. Did not find values    **;
            ** for all lab sasnames in normlab hence had to go to         **;
            ** individual views and not get all data from normlab         **;
            ****************************************************************;
            %macro normext(dcmsub=,labvar1=,labvar2=,labvar3=,dsn=);
              ** generate char string for SQL stmt where clause **;
              %let dcmsubs=%str(%')%upcase(&dcmsub)%str(%');
              %let labvars=%str(%')%upcase(&labvar1)%str(%');
            
              %do i= 2 %to 3;
                %if %length(&&labvar&i) > 0 %then
                  %let labvars = &labvars%str(,%')%upcase(&&labvar&i)%str(%');
              %end;
            
              proc sql;
               connect to oracle;
                ** apparently sample date is same as dcm date. Date is assigned **;
                ** to the DCM that appears on the top of LE1 so haemotalogy     **;
                ** will be assigned the date. Thats in the LE1 view.            **;
                create table &dsn  as
                select * from connection to oracle
                  (select to_number(pt) pt, sasname, dcmname, dcmsubnm,
                        dcmdate, dcmtime, cpevent, actevent,
                        repeatsn, numval, labunit, rngflag, lowrang, hirang
                   from &study$current.normlab
                   where sasname in ( &labvars )
                   and dcmsubnm = &dcmsubs )
                order by pt;
              quit;
            
            %mend;
            %normext(dsn=scrturea,dcmsub=LE1,labvar1=SCRT,labvar2=UREA); ** serum creat & BUN **;
            %normext(dsn=smglob,dcmsub=LE7,labvar1=GLOB2); ** serum microglobulun**;
            
            data normlab;
             set scrturea;
            run;
            
            proc append base=normlab data=smglob force;
            run;
            
            %normext(dsn=ucrt5,dcmsub=LE5,labvar1=UCRT); ** urine creat day 1**;
            %normext(dsn=ucrt6,dcmsub=LE6,labvar1=UCRT); ** urine creat day 28 **;
            %normext(dsn=usmp5,dcmsub=USMP1,labvar1=SMPLWGT); ** urine vol day 1 **;
            %normext(dsn=usmp6,dcmsub=USMP2,labvar1=SMPLWGT); ** urine vol day 28 **;
            
            ***************************************************************;
            ** This macro computes 24 hr creatinine excretion            **;
            ***************************************************************;
            %macro ucrt(day,lenum);
              proc sort data=ucrt&lenum;
                by pt repeatsn;
              run;
            
              proc sort data=usmp&lenum;
                by pt repeatsn;
              run;
            
              data ucrt&day;
                merge ucrt&lenum
                      usmp&lenum (rename=(numval=smplwgt));
                by pt repeatsn;
                retain ucrt24;
                ** Urine sample is expressed in weight (mg) so convert to litre **;
                ** assuming density of urine is 1                               **;
                if first.pt then ucrt24=smplwgt*numval*.001;
                else ucrt24 = ucrt24 + smplwgt*numval*.001;
              run;
            
              proc sort data=ucrt&day;
                by pt descending repeatsn;
              run;
            
              data uc24_&day (keep=pt sasname dcmname dcmsubnm
                               dcmdate dcmtime cpevent actevent
                               repeatsn labunit rngflag lowrang hirang
                               ucrt24 rename=(ucrt24=numval));
                set ucrt&day (drop=numval);
                by pt descending repeatsn;
                if first.pt;
                sasname='UCRT24';
                labunit='MG/24 HRS';
              run;
            
              proc format;
                value repeat
                 1 = '00-06 hrs'
                 2 = '06-12 hrs'
                 3 = '12-24 hrs'
                 ;
              run;
            
              ** preparing to append creatine concentration data **;
              data ucrt&lenum;
               set ucrt&lenum;
               cpevent=trim(left(cpevent)) || ' ' || put(repeatsn,repeat.);
               ** need to check reasonable vals for actevent that are not likely to be assigned **;
               actevent=actevent+repeatsn/1000;
              run;
            
              ** preparing to append urine sample weight data **;
              data usmp&lenum;
               set usmp&lenum;
               cpevent=trim(left(cpevent)) || ' ' || put(repeatsn,repeat.);
               ** need to check reasonable vals for actevent that are not likely to be assigned **;
               actevent=actevent+repeatsn/1000;
              run;
            
              proc append base=normlab data=ucrt&lenum force;
              run;
            
              proc append base=normlab data=usmp&lenum force;
              run;
            %mend;
            %ucrt(1,5);
            %ucrt(28,6);
            
            proc append base=normlab data=uc24_1 force;
            run;
            
            proc append base=normlab data=uc24_28 force;
            run;
            
            ****************************************************************************;
            ** Manipulate urine wt (USMP1,2) and creatinine conc (LE5,LE6) to fit     **;
            ** into normlab struc. This is cuz USMP1 and LE5 are for Day 1 and USMP2  **;
            ** and LE6 are for Day 28, one has to add  ?????????????????????          **;
            ****************************************************************************;
            
            ****************************************************************************;
            ** Extract data for 24hr beta2 microglobulin, 24hr protein, 24hr albumin  **;
            ****************************************************************************;
            %normext(dsn=umglob,dcmsub=LE4,labvar1=UGLOB22,labvar2=UCRT2,labvar3=UALB2);
            
            proc sort data=uc24_1;
             by pt;
            run;
            
            proc sort data=uc24_28;
             by pt;
            run;
            
            ***********************************************************;
            ** Use 24 hr Creatinin from day 1 for Day1 and screening **;
            ** Use 24 hr Creatinin from day 28 for day 28            **;
            ***********************************************************;
            data umglob(drop=uc24_1 uc24_28);
             merge uc24_1(in=in_uc keep=pt cpevent actevent numval rename=(numval=uc24_1))
                   uc24_28(in=in_uc keep=pt cpevent actevent numval rename=(numval=uc24_28))
                   umglob(in=in_norm);
             by pt;
             if in_norm;
             ** uc24_1 and uc24_8 is now in mg/24 hrs, and numval which are ratios **;
             ** for UGLOB22,UCRT2 and UALB2 are in per gm, so take product and     **;
             ** divide by 1000 to get wt per 24 hrs                                **;
             if cpevent='SCREENING' or cpevent='DAY 1' then numval=(numval*uc24_1)/1000;
             else if cpevent='DAY 28' then numval=(numval*uc24_28)/1000;
            run;
            
            proc append base=normlab data=umglob force;
            run;
            
            *******************************************************************;
            ** Compute creatinine clearance based on uc24 and SCRT values    **;
            *******************************************************************;
            data crtclear (drop=uc24_1 uc24_28);
              merge uc24_1(in=in_uc keep=pt cpevent actevent numval rename=(numval=uc24_1))
                    uc24_28(in=in_uc keep=pt cpevent actevent numval rename=(numval=uc24_28))
                    scrturea(in=in_scrt where=(sasname='SCRT'));
             by pt;
             sasname='CRTCLR';
             labunit='ML/MIN';
             if in_scrt;
             if cpevent='DAY 28' then numval=(uc24_28/(numval/8840))/1440;
             else numval=(uc24_1/(numval/8840))/1440;
            run;
            
            proc append base=normlab data=crtclear;
            run;
            
            *******************************************************************;
            ** Obtain Spot protein values if protein dipstick value in LE2   **;
            ** is greater than 1+, want to report this anyway just to confir **;
            ** since this is a char val and everything else is number may    **;
            ** have to use different program and report statements           **;
            *******************************************************************;
            *normext(dsn=uproqlt,dcmsub=LE2,labvar1=UPROQLT);
            
            ** get just spot protein data for now **;
            %normext(dsn=le8prot,dcmsub=LE8,labvar1=UCRT2);   ** urine protein **;
            
            ** need to multiply with appropriate 24hr creatinine excretion **;
            data le8prot (drop=uc24_1 uc24_28);
             merge  uc24_1(in=in_uc keep=pt cpevent actevent numval rename=(numval=uc24_1))
                    uc24_28(in=in_uc keep=pt cpevent actevent numval rename=(numval=uc24_28))
                    le8prot (in=in_norm);
             by pt;
             if in_norm;
             if cpevent='DAY 28' then numval=(numval*uc24_28)/1000;
             else numval=(numval*uc24_1)/1000;
             ** had to rename data from LE8 to someother val cuz data **;
             ** in le4 also has UCRT2 val, and in current ver not     **;
             ** selecting by dcmname                                  **;
             sasname='SPROT';
            run;
            
            proc append base=normlab data=le8prot force;
            run;
            
            proc sql;
             connect to oracle;
              ** get earliest dosing record for DAY1. This will be used  **;
              ** to compare with sampling date to make sure only samples **;
              ** taken before DAY1 dose will be treated as baseline      **;
              create table dosing as
              select * from connection to oracle
                (select to_number(pt) pt, mtbegdc dosedt, mtbegtc dosetm
                 from &study$current.mtdmtd
                 where mtpnam='DAY 1-AM DOSE')
              order by pt;
             disconnect from oracle;
            quit;
            
            proc sort data=normlab;
              by pt;
            run;
            
            ** Check to see if baseline measurements were taken after   **;
            ** baseline date and time. need to have logic to handle     **;
            ** these records. Fortunately none right now! Ask David     **;
            data badbase;
              merge normlab(where=(cpevent='DAY 1'))
                    dosing;
              by pt;
              if dcmdate > dosedt or (dcmdate=dosedt and dcmtime > dosetm);
            run;
            
            ** david asked for study dosing date var which is assessment date **;
            ** minus first dosing date plus 1. need to add that here          **;
            
            proc sql;
             title1 'Number of records where Baseline Lab Data was collected after Day 1';
             select count(*) from badbase;
            quit;
            
            ** If actevent has decimal values then it means test was redone.   **;
            ** For listing report all tests, but for summaries use the latest  **;
            ** test, per David. Hence using existing actevent vals             **;
            data labdose;
              merge normlab(in=in_norm)
                    dat16074.rnd16074 (in=in_rand);
              by pt;
              if in_norm;
              ** need to create new var cuz using pattern for grouping is ok but **;
              ** for sorting not ok since it puts placebo at bottom.             **;
              trtgrp=input(put(pattern,$trt_c.),8.);
            
              ** creating new actevent var just to capture integer vals **;
              ** since for summaries only the last rec will be used     **;
              newact=int(actevent);
            run;
            
            
            ** need to add logic here so that only summary reports use newact   **;
            ** need to get to this after listing is complete                    **;
            proc sort data=labdose;
              by sasname trtgrp pt repeatsn cpevent newact actevent;
            run;
            
            data labdose;
             set labdose;
            *by sasname trtgrp pt repeatsn cpevent newact;
            *if last.newact;
            run;
            
            
            proc sort data=labdose;
              by sasname trtgrp pt cpevent actevent;
            run;
            
            ** must ensure there are not multiple records based on repeatsn at this stage **;
            data labprint;
              set labdose;
              by sasname trtgrp pt cpevent;
              retain baseline;
              ** remove formats or else causing extra space in graph Y axis label **;
              format numval chngbase chngper;
              if first.pt then baseline=.;
              ** in case multiple records for cpevent ie actevent x.01 then use the last value**;
              if cpevent='DAY 1' and last.cpevent then baseline=numval;
              else if cpevent ne 'SCREENING' then
              do;
                chngbase=numval-baseline;
                chngper=100*(chngbase/baseline);
              end;
            run;
            
            proc sort data=labprint;
              by trtgrp pt cpevent actevent;
            run;
            
            ** create dynamic format for actevent. needed to place this **;
            ** here as opposed to formats.sas cuz formats.sas runs at   **;
            ** the begining before these datasets are created           **;
            proc sql;
             create table fmt as
             select distinct(actevent) as start, cpevent as label
             from labprint;
            quit;
            
            data fmt;
             set fmt;
             fmtname='cp_n';
             type='N';
             length decimal $ 10;
             if start-int(start) > 0 then decimal=put(start-int(start),4.3);
             else decimal='';
             if decimal ne ''
               then label=trim(label) || substr(decimal,index(decimal,'.'));
            run;
            
            proc format cntlin=fmt;
            run;
            
            options nomacrogen nosymbolgen nomlogic nomprint;
            **********************************************************************;
            ** Listing Report                                                   **;
            ** Put in macro cuz needed to use macro if then logic in report step**;
            **********************************************************************;
            %macro listrpt(blind=N,fpref=,labtest=,fname=,change=,labvar1=,
                           labvar2=,labvar3=,labvar4=);
            
              options pageno=1 missing=' ' ls=132 ps=50;
            
              %if %upcase(&change)=Y %then %let change=%str(chngbase chngper);
              %else %let change=;
            
              %if %upcase(&labtest)=SERUM %then
                      %let whr = %str((where=(sasname in ('SCRT','UREA','GLOB2','CRTCLR'))));
              %else %if %upcase(&labtest)=URINE %then
                 %let whr=%str((where=(sasname in ('UGLOB22','UCRT2','UALB2','UCRT24'))));
              %else %if %upcase(&labtest)=%str(URINE_CREATININE) %then
                 %let whr=%str((where=(sasname in ('SMPLWGT','UCRT'))));
              %else %if %upcase(&labtest)=%str(SPOT_PROTEIN) %then
                 %let whr=%str((where=(sasname in ('SPROT'))));
              %else %if %upcase(&labtest)=%str(BUN_CREATININ) %then
                 %let whr=%str((where=(sasname in ('SCRT','UREA'))));
            
              %if %upcase(&blind)=Y %then %let fname=_&fname._blinded;
              %else %let fname=_&fname;
            
              filename prntout  "&compound:[&study..interim1.adhoc.tablib]&fpref.lab_list&fname..tab";
            
              proc printto print=prntout new;
              run;
            
              %tf(t,1,%str(&study - Interim Analysis),l);
              %tf(t,2,%str(          &compound),l);
              %tf(t,3,Listing of &labtest Lab Data,c);
              %tf(t,4,%str(Actual Measurement, Change from Baseline and % Change from Baseline (Day 1)),c);
              %tf(t,5,,);
              %tf(t,6,_,x);
              %tf(f,1,_,x);
              %tf(f,2, ,l);
              %tf(f,3,%str(NOTE: Change=Follow-up Measurement - Baseline Measurement (Day 1) , % Change = Change / Baseline (Day 1)),l);
              %tf(f,4,%str(      (R)=Repeated Sample  @ indicates the record is excluded from summary analysis),l);
              %tf(f,5,%str(      H/L=Actual measurement is above/below normal range),l);
              %tf(f,6,,);
              %tf(f,7,,s);
              %tf(f,8,,t);
            
              proc report data=labprint &whr
                          nowd headline headskip ls=132 ps=50 split='*' center;
                column trtgrp pt actevent sasname,(numval rngflag &change          );
                by notsorted;
                break after pt       / skip;
                break after trtgrp   / skip;
                define trtgrp   / 'Treatment*Group' group order=internal format=trt_n. width=15;
                define pt       / 'Patient*Id' group width=10 format=z3.;
                define actevent / 'Scheduled*Time' group format=cp_n. order=internal id width=15;
                define sasname  / across format=$labfmtr. order=internal '- LAB TEST -';
                define numval   / 'Value' format=8.2 width=8 spacing=7;
                define rngflag  / ' ' width=1 format=$rngflg.;
                define chngbase / 'Change' format=8.1 width=8 ;
                define chngper  / '% Change' format=pctfmt. width=8;
                compute pt;
                 %if %upcase(&blind)=Y %then %str(pt=int(1.2469*pt););
                endcompute;
              run;
            
              %tf(t,0,,);
              %tf(f,0,,);
            
              proc printto;
              run;
            
              filename prntout clear;
            %mend listrpt;
            %listrpt(blind=n,fpref=&env,labtest=Serum,fname=serum,change=Y);
            %listrpt(blind=n,fpref=&env,labtest=Urine,fname=urine,change=Y);
            %listrpt(blind=n,fpref=&env,labtest=%str(Urine_Creatinine),fname=urine_creat,change=Y);
            %listrpt(blind=n,fpref=&env,labtest=%str(Spot_Protein),fname=spot_protein,change=Y);
            %listrpt(blind=n,fpref=&env,labtest=%str(BUN_Creatinin),fname=bun_creatinin,change=Y);
            
            %listrpt(blind=y,fpref=&env,labtest=Serum,fname=serum,change=Y);
            %listrpt(blind=y,fpref=&env,labtest=Urine,fname=urine,change=Y);
            %listrpt(blind=y,fpref=&env,labtest=%str(Urine_Creatinine),fname=urine_creat,change=Y);
            %listrpt(blind=y,fpref=&env,labtest=%str(Spot_Protein),fname=spot_protein,change=Y);
            %listrpt(blind=y,fpref=&env,labtest=%str(BUN_Creatinin),fname=bun_creatinin,change=Y);
            
            ******************************************************;
            ** Scatter Plots                                    **;
            ******************************************************;
            %macro scatter(labvar=,calctype=,num=,fpref=);
            
              ** assign symbols for scatter plot **;
              %sym(none,.5);
            
              %if %upcase(&calctype)=ACT %then
              %do;
                %let graphvar=numval;
                %let whr=;
              %end;
              %else
              %do;
                %let graphvar=chngbase;
                %let whr = (where= (cpevent in ('DAY 7','DAY 14', 'DAY 21', 'DAY 28')));
              %end;
            
              proc sort data=labprint(where=(sasname="&labvar")) out=le1plot;
                by actevent;
              run;
            
              ** deliberately having an extra step here to prepare data **;
              data le1plot;
               set le1plot &whr;
               ** to avoide decimals actevent **;
               actevent=int(actevent);
              run;
            
              proc sort data=le1plot;
                by actevent;
              run;
            
              data le1plot1;
               set le1plot;
               by actevent;
               retain newtime intact;
               ** intact is an integer counter for each set of actevents **;
               ** newtime is the corresponding real number               **;
               if _n_=1 then intact=1;
               if first.actevent then
               do;
                 intact=intact+1;
                 newtime=intact;
               end;
               else newtime=newtime+(round(ranuni(1),.01)-.5)/3;
              run;
            
              ** for title in proc report **;
              data _null_;
               call symput('var1',trim(put(upcase("&labvar"),$labfmt.)));
               call symput('var2',trim(put(upcase("&labvar"),$labunit.)));
               call symput('var3',trim(put(upcase("&calctype"),$stat_d.)));
               call symput('fname',compress(put(upcase("&labvar"),$labfmt.)));
              run;
            
              filename grafout  "&compound:[&study..interim1.adhoc.cgmlib]&fpref.&num.scat_&calctype._&fname..cgm";
            
              title1 h=1.25   j=c ls=.25 "Scatter Plot of &var3 Over Time";
              title2 ' ';
              title3 h=.75 j=c "&var1 (&var2)";
              title4 h=.75     " ";
            
              %tf(gf,1,,s);
              %tf(gf,2,,g);
            
              proc gplot data=le1plot1;
                plot &graphvar * newtime  = trtgrp / vaxis=axis3 hminor=0 legend=legend1
                     %if %upcase(&calctype)=CHNG %then %str(haxis=axis2 vref=0 lvref=1;);
                     %else %if %upcase(&calctype)=ACT %then %str(haxis=axis1;);
                format trtgrp trt_n.;
              run;
              quit;
            
              %tf(t,0,,);
              %tf(f,0,,);
            %mend;
            %scatter(labvar=SCRT,calctype=act,num=,fpref=&env);
            %scatter(labvar=UREA,calctype=act,num=,fpref=&env);
            %scatter(labvar=SCRT,calctype=chng,num=,fpref=&env);
            %scatter(labvar=UREA,calctype=chng,num=,fpref=&env);
            
            
            **********************************************************************;
            ** Descriptive Statistics (Univariate) Summary Report               **;
            ** calcvar is the analysis variable - numval for raw data,          **;
            **      chngbase for change from baseline and chngper for percent   **;
            **      change from baseline                                        **;
            ** order Order in which these should show up in the report          **;
            ** keeping both cpevent and actevent for now                        **;
            **  Macro called by univarpt                                        **;
            **********************************************************************;
            %macro univar(calcvar,order,labvar);
              proc sort data=labprint (where=(sasname=upcase("&labvar"))) out=le1univ;
               by trtgrp pt cpevent actevent;
              run;
            
              ** was told by David to use the last actevent in case of multiples **;
              ** for summarization.                                              **;
              data le1univ;
               set le1univ;
               by trtgrp pt cpevent actevent;
               if last.cpevent;
               ** this is cuz in some case pt had only actevent=x.yy **;
               ** so these records were selected based on above      **;
               ** condition and caused probs in transpose at rpttran **;
               actevent=int(actevent);
              run;
            
              proc sort data=le1univ;
                by trtgrp cpevent actevent;
              run;
            
              proc univariate data=le1univ noprint;
                by trtgrp cpevent actevent;
                var &calcvar;
                output out=univar
                       n=n mean=mean median=median min=min max=max
                       stdmean=stdmean q1=q1 q3=q3 p90=p90 p95=p95 p99=p99 ;
              run;
            
            
              options missing='*';
              ** meanse and ci95 datasets will also be used for HiLo plots **;
             ** for raw data and change from baseline create dataset for hilo plots **;
              data
                %if %upcase(&calcvar)=NUMVAL %then
                  %str( hiloval (keep=trtgrp cpevent actevent mean stdmean ci95) );
                %else %if %upcase(&calcvar)=CHNGBASE %then
                  %str( hilochng(keep=trtgrp cpevent actevent mean stdmean ci95)
                        psumchng(keep=trtgrp cpevent actevent p90 p95 p99) );
                   meannse(keep=trtgrp cpevent actevent meannse)
                   medianq(keep=trtgrp cpevent actevent medianq)
                   minmax (keep=trtgrp cpevent actevent minmax)
                   percent(keep=trtgrp cpevent actevent percent)
                   ci95c  (keep=trtgrp cpevent actevent ci95c);
               set univar;
               length meannse medianq minmax percent ci95c $ 20;
               ci95 = tinv(.975,n-1)*stdmean;
               hi=mean+ci95;
               lo=mean-ci95;
               ci95c   = compress(put(ci95,6.1)) || ' (' || compress(put(hi,6.1)) || ', ' || compress(put(lo,6.1)) || ')';
               meannse = compress(put(mean,6.1)) || ' (' || compress(put(n,3.)) || ', ' || compress(put(stdmean,5.1)) || ')';
               medianq = compress(put(median,6.1)) || ' (' || compress(put(q1,6.1)) ||  ', ' || compress(put(q3,6.1)) || ')';
               minmax = '(' || compress(put(min,6.1)) || ', ' || compress(put(max,6.1)) || ')';
               percent = compress(put(p90,6.1)) || ',' || compress(put(p95,6.1)) || ',' || compress(put(p99,6.1));
               ** Screening and Day 1 should show blank for change and percent change **;
               %if %upcase(&calcvar) = CHNGBASE or %upcase(&calcvar) = CHNGPER %then
               %do;
                   if actevent in (1,2) then
                   do;
                     ** reset values to blank instead of * **;
                     ci95c=''; meannse=''; medianq=''; minmax=''; percent='';
                   end;
                %end;
              run;
            
              ************************************************************************************;
              ** need to unstring the dataset, using macro cuz there is no doover in macro lang **;
              ** var2  the munged statistical analysis masures                                  **;
              ** ord2  the order in which they should appear in the report                      **;
              ************************************************************************************;
              %macro dostat(var2,ord2);
                data &var2;
                 set &var2(rename=(&var2=value));
                 ** to insure proper sort order in rpt. no need for this var to be char **;
                 length stat $ 2;
                 stat="&ord2";
                run;
              %mend dostat;
              %dostat(meannse,01);
              %dostat(medianq,02);
              %dostat(minmax,03);
              %dostat(percent,04);
              %dostat(ci95c,05);
            
              data &calcvar;
               set meannse medianq minmax percent ci95c;
               ** this ensures proper sort order in the report **;
               vartyp = ℴ
              run;
            %mend univar;
            
            ***********************************************************************;
            ** Percentile Summary Report                                         **;
            **   Why not have cpevent as well here.. need to chk cpevent/actevent**;
            **   Called from within Desc Stat summary report cuz of dependancy of**;
            **   intermediate datasets created by desc stat                      **;
            ** called from univarpt                                              **;
            ***********************************************************************;
            %macro perrpt(labvar=,fpref=);
             ** where clause for change variables **;
             %let whr = %str(cpevent in ('DAY 7','DAY 14', 'DAY 21', 'DAY 28'));
            
              proc sort data=labprint (where=(sasname=upcase("&labvar"))) out=le1percg;
                by trtgrp pt cpevent actevent;
              run;
            
              ** was told by David to use the last actevent in case of multiples **;
              ** for summarization.                                              **;
              data le1percg;
               set le1percg;
               by trtgrp pt cpevent actevent;
               if last.cpevent;
               ** this is cuz in some case pt had only actevent=x.yy **;
               ** so these records were selected based on above      **;
               ** condition and caused probs in transpose at rpttran **;
               actevent=int(actevent);
              run;
            
              proc sort data=le1percg;
                by trtgrp actevent;
              run;
            
              proc sort data=psumchng;
                by trtgrp actevent;
              run;
            
              data range;
                merge le1percg(where=(sasname="&labvar" and &whr ))
                      psumchng(where=( &whr ));
                by trtgrp actevent;
                ** count records which hit High water mark **;
                if rngflag='H' then hc=1;
                else hc=0;
            
                ** count records where there was a positive change **;
                if chngbase > 0 then cc=1;
                else cc=0;
            
                ** count records which cross percentile marks **;
                if chngbase > p90 then n90=1;
                else n90=0;
                if chngbase > p95 then n95=1;
                else n95=0;
                if chngbase > p99 then n99=1;
                else n99=0;
              run;
            
              proc summary data=range nway missing;
               class trtgrp actevent;
               var hc cc n90 n95 n99;
               output out=rngsum sum=;
              run;
            
              data prn;
                merge rngsum(in=in_sum rename=(_freq_=num) drop=_type_)
                      psumchng(rename=(p90=val90 p95=val95 p99=val99));
                by trtgrp actevent;
                if in_sum;
                ** rename vars to val above cuz p_ denotes percentage here **;
                array p(5) p_hc p_cc p_n90 p_n95 p_n99;
                array v(5) hc cc n90 n95 n99;
                do i = 1 to 5;
                  p(i)=v(i)/num;
                end;
              run;
            
              data _null_;
               call symput('var1',trim(put(upcase("&labvar"),$labfmt.)));
               call symput('fname',compress(put(upcase("&labvar"),$labfmt.)));
              run;
            
              options pageno=1 missing=' ' ls=132 ps=50;
            
              filename prntout  "&compound:[&study..interim1.adhoc.tablib]&fpref.lab_per_sum_&fname..tab";
            
              proc printto print=prntout new;
              run;
            
              %tf(t,1,%str(&study - Interim Analysis),l);
              %tf(t,2,%str(          &compound),l);
              %tf(t,3,%str(Summary of Percentiles for &var1 Lab Data),c);
              %tf(t,4,Change from Mean of Baseline,c);
              %tf(t,5,,);
              %tf(t,6,_,x);
              %tf(f,1,_,x);
              %tf(f,2,,);
              %tf(f,3,,s);
              %tf(f,4,,t);
            
              proc report data=prn nowd headline headskip center ls=132 ps=50 split='*';
                column trtgrp actevent num
                    (' > High' 'Normal Range' hc p_hc)
                    ('Lab Result' 'Increased' 'Post Dose' cc p_cc)
                    ('> 90% Change' 'from Baseline' val90 n90 p_n90)
                    ('> 95% Change' 'from Baseline' val95 n95 p_n95)
                    ('> 99% Change' 'from Baseline' val99 n99 p_n99);
                break after trtgrp   / skip;
                break after actevent / skip;
                define trtgrp   / 'Group' group order=internal format=trt_n.  width=12;
                define actevent / 'Event' group order=internal format=cp_n.    width=10;
                define num      / '#*Subj' width=4 format=4.;
                define hc       / 'N' width=3 spacing=3;
                define p_hc     / '%' width=6 format=percent6.1;
                define cc       / 'N' width=3 spacing=3;
                define p_cc     / '%' width=6 format=percent6.1;
                define val90    / '90%' width=6 format=5.1 spacing=4;
                define n90      / 'N' width=3;
                define p_n90    / '%' width=7 format=percent6.1;
                define val95    / '95%' width=6 format=5.1 spacing=4;
                define n95      / 'N' width=3;
                define p_n95    / '%' width=7 format=percent6.1;
                define val99    / '99%' width=6 format=5.1 spacing=4;
                define n99      / 'N' width=3;
                define p_n99    / '%' width=7 format=percent6.1;
              run;
            
              %tf(t,0,,);
              %tf(f,0,,);
            
              proc printto;
              run;
            
              filename prntout clear;
            %mend;
            
            ****************************************************************;
            ** HiLo Plots                                                 **;
            ** Called for within univarpt                                 **;
            ****************************************************************;
            %macro hilo(labvar=,calctype=,stat=,num=,fpref=);
            
              ** assign symbols for hilo plot **;
              %sym(hilo,.5);
            
              filename grafout  "&compound:[&study..interim1.adhoc.cgmlib]&fpref.&num.hilo_&calctype._&stat._&labvar..cgm";
            
              %if %upcase(&calctype)=ACT %then
              %do;
                %let graphvar=numval;
                %let whr=;
              %end;
              %else
              %do;
                %let graphvar=chngbase;
                %let whr = (where= (cpevent in ('DAY 7','DAY 14', 'DAY 21', 'DAY 28', 'DAY 33(TERM)')));
              %end;
            
              ** dropping cpevent here for now.. need to establish cpevent/actevent logic **;
              data sumstat(drop=mean ci95 stdmean cpevent);
               %if %upcase(&calctype)=ACT %then %str(set hiloval &whr;);
               %else %if %upcase(&calctype)=CHNG %then %str(set hilochng &whr;);
               cilo= mean - &stat;
               cihi= mean + &stat;
              run;
            
              proc sort data=sumstat;
                by actevent;
              run;
            
              ** deliberately having an extra step here to prepare data **;
              data sumstat;
               set sumstat;
               ** to avoide decimals actevent **;
               actevent=int(actevent);
              run;
            
              proc sort data=sumstat;
                by actevent;
              run;
            
              data sumstat1;
               set sumstat;
               by actevent;
               retain newtime intact;
               ** intact is an integer counter for each set of actevents **;
               ** newtime is the corresponding real number               **;
               if _n_=1 then intact=1;
               if first.actevent then
               do;
                 intact=intact+1;
                 newtime=intact;
               end;
               else newtime=newtime+(round(ranuni(1),.01)-.5)/3;
               *newtime=actevent+(.08*(trtgrp-1));
              run;
            
              proc sort data=sumstat1;
                by trtgrp actevent newtime;
              run;
            
              proc transpose data=sumstat1(drop=intact) out=sumtran;
                by trtgrp actevent newtime;
              run;
            
              ** for title in proc report **;
              data _null_;
               call symput('var1',trim(put(upcase("&labvar"),$labfmt.)));
               call symput('var2',trim(put(upcase("&labvar"),$labunit.)));
               call symput('var3',trim(put(upcase("&calctype"),$stat_d.)));
               call symput('title2',trim(put(upcase("&stat"),$stat_c.)));
              run;
            
              title1 h=1.25 j=c "Plot of Mean of &var3 &title2";
              title2 ' ';
              title3 h=.75 j=c "&var1 (&var2)";
              title4 h=.75 j=c " ";
            
              %tf(gf,1,,s);
              %tf(gf,2,,g);
            
              proc gplot data=sumtran;
                plot col1 * newtime = trtgrp /legend=legend1 vaxis=axis3 hminor=0
                     %if %upcase(&calctype)=CHNG %then %str(haxis=axis2 vref=0 lvref=1;);
                     %else %if %upcase(&calctype)=ACT %then %str(haxis=axis1;);
                format trtgrp trt_n. ;
              run;
              quit;
            
              %tf(t,0,,);
              %tf(f,0,,);
            %mend;
            
            ****************************************************************;
            ** Descriptive statistics summary report                      **;
            ** Calls macro perrpt and univar                              **;
            ****************************************************************;
            %macro univarpt(labvar=,fpref=);
              %univar(numval,1,&labvar);
              %univar(chngbase,2,&labvar);
              %univar(chngper,3,&labvar);
            
              data report;
                set numval chngbase chngper;
              run;
            
              proc sort data=report;
                by trtgrp vartyp stat;
              run;
            
              ** Had to resort to proc transpose because using actevent as across to display **;
              ** char values did not work out. Although that would be the better way to do   **;
              ** must check with SI about this                                               **;
              proc transpose data=report prefix=trt out=rpttran;
                by trtgrp vartyp stat;
                id actevent;
                var value;
              run;
            
              options pageno=1 ls=145 ps=50;
              data _null_;
               ** for title in proc report **;
               call symput('var1',compress(put(upcase("&labvar"),$labfmt.)));
               ** for naming file **;
               call symput('fname',compress(put(upcase("&labvar"),$labfmt.)));
              run;
            
              filename prntout  "&compound:[&study..interim1.adhoc.tablib]&fpref.lab_stat_sum_&fname..tab";
            
              proc printto print=prntout new;
              run;
            
              %tf(t,1,%str(&study - Interim Analysis),l);
              %tf(t,2,%str(          &compound),l);
              %tf(t,3,%str(Summary of Descriptive Statistics for &var1 Lab Data,),);
              %tf(t,4,%str(Change and % Change from Baseline (Day 1) From Univariate Analysis),);
              %tf(t,5,_,x);
              %tf(f,1,_,x);
              %tf(f,2,%str(* indicates statistic could not be computed),l);
              %tf(f,3,,s);
              %tf(f,4,,t);
            
              ** need to add logic to automatically generate transposed vars **;
              ** based on actevent values                                    **;
              proc report data=rpttran nowd headline headskip ls=132 ps=50 split='*';
                column trtgrp vartyp stat trt1 trt4 trt6 trt11 trt13 trt15;
                break after trtgrp   / skip;
                break after vartyp   / skip;
                define trtgrp   / 'Group' group order=internal format=trt_n.  width=12;
                define vartyp   / ' ' group width=10 order=internal format=vartyp.;
                define stat     / ' Statistic' group id order=internal format=$stat_e. width=18;
                define trt1       / 'Screening' width=20;
                define trt4       / 'Day 1' width=25;
                define trt6       / 'Day 7' width=25;
                define trt11      / 'Day 14' width=25;
                define trt13      / 'Day 21' width=25;
                define trt15      / 'Day 28' width=25;
              run;
            
              %tf(t,0,,);
              %tf(f,0,,);
            
              proc printto;
              run;
            
              filename prntout clear;
            
              ** call macro for percentile summary report **;
              %perrpt(labvar=&labvar,fpref=&env);
            
              ** call macro for HiLo plots **;
              %hilo(labvar=&labvar,calctype=act,stat=ci95,num=,fpref=&env);
              %hilo(labvar=&labvar,calctype=act,stat=stdmean,num=,fpref=&env);
              %hilo(labvar=&labvar,calctype=chng,stat=ci95,num=,fpref=&env);
              %hilo(labvar=&labvar,calctype=chng,stat=stdmean,num=,fpref=&env);
            %mend;
            %univarpt(labvar=SCRT,fpref=&env);
            %univarpt(labvar=UREA,fpref=&env);
            
            ** reset formchar values **;
            options formchar='|----|+|---+=|-/\<>*';