EDC - Updated Monitor Reviewed Report

[Greg Wagner]

Following is an update to the Monitor Reviewed report MonRev.SAS, which is included in our EDC training materials. These are the changes to the program:

• Dynamically gets a list of the datasets defined in the study that contain the MonitorReviewed field.
• Dynamically gets the dataset labels to be applied to the eCRF field. This is retrieved from the label defined in the Data Structure module.
• Subset data for the current users assigned site(s). This requires the use of the program, sclGetUserInvestigators.sas, which creates a macro variable containing a list of the current users sites. View the sclUserInvestigators post.
• Added format definition for Yes/No

MonRev.SAS Program Code
The program, MonRev.SAS, is also attached at the bottom of this post. This program also requires the use of the program, sclGetUserInvestigators.sas.

/***************************************************************** 
Program:     MonRev.sas
Written By:  Bob Borysko, Greg Ventura, Ronni Rubenstein
Date:           12/20/05
Purpose:     To print out the monitorreviewed status of all records
             in current study. 
             
Revised: 11/14/2008 G. Wagner

    Updated program to dynamically get a list of the datasets in the 
    study that contain the monitorreviewed field.

    Dynamically get the dataset labels to be applied to the 
    eCRF field.

    Subset results just for users current site.  This 
    requires the use of the program 
    sclGetUserInvestigators.sas which creates a 
    macro variable containing a list of the users sites. 

    Added format definition for Yes/No 
******************************************************************/ 

** clean up work directory so we don't append accidentally to existing info **;
proc datasets lib=work nolist;
    delete AllData01 AllData02 AllContents Contents;
quit;

** Initialize macro variables so code does not error if no selections exist **;
%let datasets=;
%let datasetlabels=;
%let totobs=0;    
    
%macro getData;

    **********************************************************************;
    ** get a list of all the tables that have the monitorreviewed field **;
    **********************************************************************;
    proc sql noprint;
        select datasetname, datasetlabel into :datasets separated by '~',  :datasetlabels separated by '~'    
        from pviews.datasets
        where nonCRF='No';
        
        select count(*) into :totobs
        from pviews.datasets
        where nonCRF='No';
    quit;

    %do i = 1 %to &totobs;

        %let dslabel=%qscan(&datasetlabels,&i,'~');
        %let dsnm=%qscan(&datasets,&i,'~');

        proc contents data=pviews.&dsnm noprint out=work.contents(keep=memname memlabel name);
        run;

        data contents;
            set contents(where=(name='MONITORREVIEWED'));
            memlabel="&dslabel";
        run;

        proc append base=AllContents data=contents;
        run;

    %end;


    **********************************************************************;
    ** populate macro variables with the data sets names and lables     **;
    **********************************************************************;
    proc sql noprint;
        select memname, memlabel into :datasets separated by '~',  :datasetlabels separated by '~'    
        from AllContents;
        
        select count(*) into :totobs
        from AllContents;
    quit;

    **********************************************************************;
    ** collect from each dataset the monitorreviewed status             **;
    **********************************************************************;
    %do i=1 %to &totobs;
    
        %let dsnm=%qscan(&datasets,&i,'~');
        %let dslabel=%qscan(&datasetlabels,&i,'~');
            
        data AllData01;
            length eCRF $25;
            set pviews.&dsnm (keep=investigatornumber patientnumber visitnumber pagenumber sequence monitorreviewed);         
            
            %if %length(&userInvestigators) > 0 %then %do;       
                where InvestigatorNumber in(&userInvestigators);
            %end;
            eCRF="&dslabel";
            if eCRF='' then eCRF="&dsnm";
                
            if monitorreviewed^='1' then monitorreviewed='0';
        run;
        
        proc append base=AllData02 data=AllData01;
        run;
    %end;

%mend getData;


%getData;



**********************************************************************;
** Create yesno format for output                                   **;
**********************************************************************;
proc format;
    value $_yesno_ 1='Yes' 0='No';
run;

** sort and print the data **;
proc sort data=AllData02;
       by monitorreviewed investigatornumber patientnumber visitnumber;
format monitorreviewed $_yesno_.;
title 'Monitor Review Summary';


**********************************************************************;
** Write body to users current directory                            **;
**********************************************************************;
ods html body='MonRev.htm';

proc print data=AllData02 noobs split='|';                                                                                              
        by  monitorreviewed;                                                                                                
run;
ods html close;