**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: ms_extractlabs.sas * * Created (mm/dd/yyyy): 12/19/2014 * Last modified: 12/23/2015 * Version: 1.3 * *-------------------------------------------------------------------------------------------------- * PURPOSE: * This macro will extract laboratory data from MSDD tables. * * Program inputs: * -a laboratory code mapping dataset * -dataset to extract records from * -dataset containing the list of codes to extract * * Program outputs: * -dataset containing the extracted records * * PARAMETERS: * -datafile = Name of the dataset to extract records from. * -lookfile = Name of the sas dataset containing the list of codes to extract. * -outfile = Name of the output dataset containing the extracted records. * * Programming Notes: * * * *-------------------------------------------------------------------------------------------------- * CONTACT INFO: * Mini-Sentinel Coordinating Center * info@mini-sentinel.org * *-------------------------------------------------------------------------------------------------- * CHANGE LOG: * * Version Date Initials Comment (reference external documentation when available) * ------- -------- -------- --------------------------------------------------------------- * 1.2 03/24/15 DM A laboratory observation must now have a non missing ADate * to be considered valid * * 1.3 12/23/15 DM Added changes required by the new laboratory table specs (v5) * ***************************************************************************************************; %MACRO MS_EXTRACTLABS(datafile=, lookfile=, outfile=); %put =====> MACRO CALLED: MS_EXTRACTLABS v1.3; %let message=; %ISDATA(dataset=indata.&labtable.); %IF %EVAL(&NOBS.>0) %THEN %DO; *This is to copy formats/lengths of the DP for the proc append; data &outfile.; set indata.&labtable.(obs=0) &lookfile.(obs=0 keep=RawGroup);; length Adate 4.; format Adate mmddyy10.; Adate=.; result_type=""; fast_ind=""; keep patid Adate ms_result_c ms_result_n ms_test_name ms_test_sub_category specimen_source ms_result_unit result_type fast_ind pt_loc loinc px px_codetype RawGroup; run; %END; %ELSE %DO; data &outfile.; set indata.&proctable.(obs=0 keep=Patid Adate px px_codetype) &lookfile.(obs=0 keep=RawGroup); if _N_<1; result_type=""; fast_ind=""; pt_loc=""; ms_result_c=""; ms_result_n=.; ms_test_name=""; ms_test_sub_category=""; specimen_source=""; ms_result_unit=""; loinc=""; run; %END; %MACRO GETLABRES; if ResultTyp="N" then do; *using ms_result_n; if index(RawLabResult,"<=") then do; if ms_result_n ne . and ms_result_n <= input(compress(RawLabResult,"<="), best.) then keep=1; end; else if index(RawLabResult,"<") then do; if ms_result_n ne . and ms_result_n < input(compress(RawLabResult,"<"), best.) then keep=1; end; else if index(RawLabResult,">=") then do; if ms_result_n >= input(compress(RawLabResult,">="), best.) then keep=1; end; else if index(RawLabResult,">") then do; if ms_result_n > input(compress(RawLabResult,">"), best.) then keep=1; end; else if index(RawLabResult,"~=") then do; if ms_result_n ~= input(compress(RawLabResult,"~="), best.) then keep=1; end; else if index(RawLabResult,":") then do; *cannot use "-" for range due to the potential of negative values; left =input(strip(scan(RawLabResult,1, ":")),best.); right=input(strip(scan(RawLabResult,2, ":")),best.); if left <= ms_result_n <= right then keep=1; end; else if lengthn(RawLabResult)>0 then do; if ms_result_n = input(RawLabResult, best.) then keep=1; end; else if lengthn(RawLabResult)=0 then do; keep=1; end; end; if ResultTyp="C" then do; *using ms_result_c; if lengthn(RawLabResult)=0 then do; keep=1; end; *Partial words are not allowed. Indexw was used; else if lengthn(ms_result_c) > 0 and indexw(RawLabResult, ms_result_c) > 0 then do; keep=1; end; end; %MEND GETLABRES; %MACRO CreateSeqAdate; array level $ level1-level3; length Adate 4.; format Adate mmddyy10.; do i=1 to 3 until(Adate ne .); level(i)=substr(RawLabDateType,i,1); if level(i)='L' and lab_dt ne . then Adate=lab_dt; else if level(i)='R' and result_dt ne . then Adate=result_dt; else if level(i)='O' and order_dt ne . then Adate=order_dt; end; drop i; %MEND CreateSeqAdate; %let newrun=0; %ISDATA(dataset=&lookfile.); %MACRO WRAPPER; %IF %EVAL(&NOBS.>0) %THEN %DO; %ISDATA(dataset=indata.&labtable.); %IF %EVAL(&NOBS.>0) %THEN %DO; /**********************************************************/ /* Lab extraction based on lookup - LAB01 */ /**********************************************************/ data _lookup; set &lookfile.; where substr(codetype,1,2)='01'; ResultTyp=substr(codetype,3,1); run; %ISDATA(dataset=_lookup); %IF %EVAL(&NOBS.>0) %THEN %DO; *Map ms_test_name, ms_test_sub_category, specimen_source, ms_result_unit, result_type, fast_ind and pt_loc combination from Code; *NOTE: Codes in Combo Input files not mapping to lab_lookup will be discarded; proc sort data=_lookup; by Code; run; *Code in infolder.&LABSCODEMAP. should be unique; proc sort nodupkey data=infolder.&LABSCODEMAP. out=_Map dupout=_dups; by Code; run; data _lookup; merge _lookup(in=a) _Map(in=b); by Code; if a and b; *Defensive coding; RawLabResult=upcase(RawLabResult); ms_test_name=upcase(strip(ms_test_name)); ms_test_sub_category=upcase(strip(ms_test_sub_category)); specimen_source=upcase(strip(specimen_source)); ms_result_unit=upcase(strip(ms_result_unit)); result_type=upcase(strip(result_type)); fast_ind=upcase(strip(fast_ind)); pt_loc=upcase(strip(pt_loc)); run; *Extract Lab Records (multiple merge amd look.* needed); proc sql noprint; create table _lab01 as select look.*, claim.patid, claim.order_dt, claim.lab_dt, claim.result_dt, upcase(claim.ms_result_c) as ms_result_c, claim.ms_result_n, claim.loinc, claim.px, claim.px_codetype from _lookup as look, indata.&labtable. as claim where look.ms_test_name = upcase(strip(claim.ms_test_name)) and look.ms_test_sub_category = upcase(strip(claim.ms_test_sub_category)) and look.specimen_source = upcase(strip(claim.specimen_source)) and look.ms_result_unit = upcase(strip(claim.ms_result_unit)) and look.result_type = upcase(strip(claim.result_type)) and look.fast_ind = upcase(strip(claim.fast_ind))and look.pt_loc = upcase(strip(claim.pt_loc)); quit; *Apply results criteria (if applicable); data _lab01(drop=keep left right level:); set _lab01; keep=0; %GETLABRES; if keep=1; %CreateSeqAdate; if missing(ADate) then call symput('message', "WARNING: At least one laboratory observation was excluded due to a missing ADate."); if adate ne .; *Observation must have a valid date; /*if RawCaresetting ne "" then do; if indexw(upcase(translate(RawCaresetting,'',"'")),upcase(Pt_Loc)); end;*/ run; *need to manage and overwrite &outfile. using a set statement for the first time; %IF %EVAL(&newrun.=0) %THEN %DO; data &outfile.; set _lab01; call symputx("newrun",1); run; proc datasets library=work nolist ; delete _lab01; quit; %END; %ELSE %DO; proc datasets library=work nolist ; append base=&outfile. data=_lab01 FORCE; delete _lab01; quit; %END; %END; /*********************************************************** *Lab extraction based on LOINC - LAB02; ***********************************************************/ data _loinc; set &lookfile.; where substr(codetype,1,2)='02'; ResultTyp=substr(codetype,3,1); run; %ISDATA(dataset=_loinc); %IF %EVAL(&NOBS.>0) %THEN %DO; proc sql noprint; create table _lab02 as select look.*, claim.patid, claim.order_dt, claim.lab_dt, claim.result_dt, claim.ms_result_c, claim.ms_result_n, claim.ms_test_name, claim.ms_test_sub_category, claim.specimen_source, claim.ms_result_unit, claim.result_type, claim.fast_ind, claim.pt_loc, claim.loinc, claim.px, claim.px_codetype from _loinc as look, indata.&labtable. as claim where strip(look.Code) = strip(claim.LOINC); quit; *Apply results criteria (if applicable); data _lab02(drop=keep left right level:); set _lab02; keep=0; %GETLABRES; if keep=1; %CreateSeqAdate; if missing(ADate) then call symput('message', "WARNING: At least one laboratory observation was excluded due to a missing ADate."); if adate ne .; *Observation must have a valid date; if RawCaresetting ne "" then do; if indexw(upcase(translate(RawCaresetting,'',"'")),upcase(Pt_Loc)); end; run; %IF %EVAL(&newrun.=0) %THEN %DO; data &outfile.; set _lab02; call symputx("newrun",1); run; proc datasets library=work nolist ; delete _lab02; quit; %END; %ELSE %DO; proc datasets library=work nolist ; append base=&outfile. data=_lab02 FORCE; delete _lab02; quit; %END; %END; /*********************************************************** *Lab extraction based on PX - LABXX (XX not in (01 02)); ***********************************************************/ data _lPx; set /*_clab*//* _lab*/ &lookfile.; where substr(codetype,1,2) not in('01','02'); ResultTyp=substr(codetype,3,1); codetype=substr(codetype,1,2); run; %ISDATA(dataset=_lPx); %IF %EVAL(&NOBS.>0) %THEN %DO; *Extract Lab Records (multiple merge); proc sql noprint; create table _lab03 as select look.*, claim.patid, claim.order_dt, claim.lab_dt, claim.result_dt, claim.ms_result_c, claim.ms_result_n, claim.ms_test_name, claim.ms_test_sub_category, claim.specimen_source, claim.ms_result_unit, claim.result_type, claim.fast_ind, claim.pt_loc, claim.loinc, claim.px, claim.px_codetype from _lPx as look, indata.&labtable. as claim where look.Code = claim.Px and upcase(strip(look.codetype)) = upcase(strip(claim.px_codetype)); quit; *apply Results Criteria (if applicable); data _lab03 (drop=keep left right level:); set _lab03; keep=0; %GETLABRES; if keep=1; %CreateSeqAdate; if missing(ADate) then call symput('message', "WARNING: At least one laboratory observation was excluded due to a missing ADate."); if adate ne .; *Observation must have a valid date; if RawCaresetting ne "" then do; if indexw(upcase(translate(RawCaresetting,'',"'")),upcase(Pt_Loc)); end; run; %IF %EVAL(&newrun.=0) %THEN %DO; data &outfile.; set _lab03; call symputx("newrun",1); run; proc datasets library=work nolist ; delete _lab03; quit; %let newrun=1; %END; %ELSE %DO; proc datasets library=work nolist ; append base=&outfile. data=_lab03 FORCE; delete _lab03; quit; %END; %END; *Removing the true duplicates as a record could have been extracted three time but using three different criteria; proc sort nodupkey data=&outfile.; by _ALL_; run; %END; *is labtable; %END; *is lookfile; %MEND WRAPPER; %WRAPPER; proc datasets library=WORK nowarn nolist; delete _temp _lPx _lookup _loinc; quit; %put &message.; %put NOTE: ******** END OF MACRO: MS_EXTRACTLABS v1.3 ********; %MEND MS_EXTRACTLABS;