%macro ck_vst_order(lib=sdtm, dsn=, dtvar=, byvars=, whrstr=%str(where substr(visit, 1, 5) in ('SCREE' 'CYCLE')));

    *** sort and keep scheduled visits     *****;
    proc sort data=&lib..&dsn out=&dsn; by &byvars; &whrstr; run;

    ***** find out-of-order visits   *****;    
    data &dsn.2;
        set &dsn;
        by &byvars;

        ** convert to numeric date ***;
        if length(&dtvar)=4 then &dtvar=strip(&dtvar)||'-01-01';
        else if length(&dtvar)=7 then &dtvar=strip(&dtvar)||'-01';

        if length(&dtvar)>=10 then adt=input(&dtvar, is8601da.);

        retain prevdt;
        if first.usubjid or first.&dsn.testcd then prevdt=.;

        if prevdt>adt>. then do;
            put "USERWAR" "NING: Dates are not in order " usubjid= visit= &dtvar=;
            output;
        end;
        if adt^=. then prevdt=adt;
        format adt date9.;
    run;
    
    
    ** Keep all test code records where out-of-order is found   ***;
    proc sort data=&dsn.2 out=&dsn.3 nodupkey; by usubjid &dsn.testcd; run;
	proc sort data=&dsn; by usubjid &dsn.testcd; run;
    data &dsn.4;
        merge &dsn.(in=a) &dsn.3(in=b keep=usubjid &dsn.testcd);
        by usubjid &dsn.testcd;
        if a and b;
    run;


    *** create final dataset for output     *****;
    proc sort data=&dsn.2 out=&dsn.3b nodupkey; by &byvars; run;
    data &dsn._out;
        merge &dsn.4(in=a) &dsn.3b(in=b keep=&byvars);
        by &byvars;
        if a;
        if a and b then flag='Y';
        keep &byvars &dtvar flag;
    run;
		
	proc export data=&dsn._out outfile= "./studyid_visits_outoforder.xlsx" dbms=xlsx 
		replace;
		sheet="&dsn";
	run;
%mend ck_vst_order;


%ck_vst_order(dsn=eg, dtvar=egdtc, byvars=usubjid egtestcd egtest visitnum visit);

*** Add temp var extestcd     ****;
data ex;
    set sdtm.ex;
    extestcd='DOSE';
run;
%ck_vst_order(lib=work, dsn=ex, dtvar=exstdtc, byvars=usubjid visitnum visit);

%ck_vst_order(dsn=lb, dtvar=lbdtc, byvars=usubjid lbtestcd lbtest visitnum visit lbdtc);
%ck_vst_order(dsn=qs, dtvar=qsdtc, byvars=usubjid qscat qstestcd qstest visitnum visit);
%ck_vst_order(dsn=rs, dtvar=rsdtc, byvars=usubjid rstestcd rstest visitnum visit rsdtc,
              whrstr=%str(where substr(visit, 1, 5) in ('SCREE' 'TUMOR')));
%ck_vst_order(dsn=tr, dtvar=trdtc, byvars=usubjid trtestcd trtest visitnum visit trdtc,
              whrstr=%str(where substr(visit, 1, 5) in ('SCREE' 'TUMOR')));
%ck_vst_order(dsn=tu, dtvar=tudtc, byvars=usubjid tutestcd tutest visitnum visit tudtc,
              whrstr=%str(where substr(visit, 1, 5) in ('SCREE' 'TUMOR')));
%ck_vst_order(dsn=vs, dtvar=vsdtc, byvars=usubjid vstestcd vstest visitnum visit vstptnum vstpt);