/*-------------------------------------------------------------------------------------*\
| PROGRAM NAME: |
| scdm_data_qa_review-labs.sas |
| |
|---------------------------------------------------------------------------------------|
| PURPOSE: |
| The purpose of this program is to perform general Laboratory Result table data |
| checks and gather information on data characteristics by resulted lab test. |
|---------------------------------------------------------------------------------------|
| PROGRAM INPUT: |
| see 00.0_scdm_data_qa_review_master_file.sas |
| |
| PROGRAM OUTPUT: |
| see Workplan PDF |
|---------------------------------------------------------------------------------------|
| CONTACT: |
| Sentinel Coordinating Center |
| info@sentinelsystem.org |
\*-------------------------------------------------------------------------------------*/
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;
* PLEASE DO NOT EDIT BELOW WITHOUT CONTACTING THE SENTINEL OPERATIONS CENTER ;
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;
/**********************************************************************************/
/* Define hierachy for Laboratory Result Table date selection */
/* Default order of preference: 1-lab_dt 2-result_dt 3-order_dt */
/**********************************************************************************/
/*--------------------------------------------------------------------------------*\
* Establish a temporary subset of SCDM LAB variables to work with or review
* Create derived variable TestDate based on 3 date fields using the hierarchy
defined at the start of the module
\*--------------------------------------------------------------------------------*/
%let qa_table= ;
%let qa_table_1= ;
%let qa_table_2= ;
%macro get_qa_table ;
%if &do_partitions and not %eval(&module in &tabid_exc) %then %do ;
data t_view /view=t_view ;
set qadata.&&&module.table.:(keep=ms_test_name result_type ms_test_sub_category fast_ind
specimen_source loinc stat pt_loc result_loc px_codetype order_dt lab_dt lab_tm
result_dt result_tm orig_result ms_result_c ms_result_n modifier orig_result_unit
std_result_unit ms_result_unit norm_range_low modifier_low norm_range_high modifier_high abn_ind) ;
;run;
%let qa_table = t_view ;
%end ;
%else %do;
%let qa_table = qadata.&&&module.table(keep=ms_test_name result_type ms_test_sub_category fast_ind
specimen_source loinc stat pt_loc result_loc px_codetype order_dt lab_dt lab_tm
result_dt result_tm orig_result ms_result_c ms_result_n modifier orig_result_unit
std_result_unit ms_result_unit norm_range_low modifier_low norm_range_high modifier_high abn_ind) ;
%end;
%mend;
%get_qa_table ;
data _null_;
length labdt resdt orddt labtm restm 3 yearmonth $7;
if _n_ = 1 then do;
/* msoc.&module._l2_record_count */
declare hash reccount(ordered:'a', hashexp:8);
reccount.DefineKey('key1');
reccount.DefineData('ms_test_name','result_type','ms_test_sub_category', 'fast_ind',
'specimen_source', 'loinc', 'count');
reccount.DefineDone();
/* msoc.&module._l2_test_units */
declare hash testunit(ordered:'a', hashexp:8);
testunit.DefineKey('key2');
testunit.DefineData('ms_test_name','result_type','ms_test_sub_category', 'fast_ind',
'specimen_source', 'orig_result_unit', 'std_result_unit', 'ms_result_unit', 'count');
testunit.DefineDone();
/* &module._l2_temp_ms_result */
declare hash msres(hashexp:8);
msres.DefineKey('key3');
msres.DefineData('ms_test_name','result_type','ms_test_sub_category', 'fast_ind',
'specimen_source', 'loinc', 'orig_result', 'ms_result_c', 'modifier',
'ms_result_n', 'ms_result_unit', 'count');
msres.DefineDone();
/* dplocal.&module._l2_mapped_tests */
declare hash maptest(ordered:'a', hashexp:8);
maptest.DefineKey('ms_test_name','result_type','loinc');
maptest.DefineData('ms_test_name','result_type','loinc', 'n');
maptest.DefineDone();
/* dplocal.&module._testdates_ym */
declare hash testym(ordered:'a', hashexp:8);
testym.DefineKey('yearmonth');
testym.DefineData('yearmonth','count');
testym.DefineDone();
/* dplocal.temp_date_all */
declare hash dtym(hashexp:8);
dtym.DefineKey('ms_test_name','result_type','lab_dt','result_dt','order_dt');
dtym.DefineData('ms_test_name','result_type','lab_dt','result_dt','order_dt','n');
dtym.DefineDone();
/* msoc.&module._l3_record_lrodt_ptloc_ym */
declare hash lropat(ordered:'a', hashexp:8);
lropat.DefineKey('ms_test_name','result_type', 'ms_test_sub_category', 'fast_ind', 'pt_loc', 'yearmonth');
lropat.DefineData('ms_test_name','result_type', 'ms_test_sub_category', 'fast_ind', 'pt_loc', 'yearmonth', 'count');
lropat.DefineDone();
/* msoc.&module._l3_record_lrodt_ym */
declare hash lroym(ordered:'a', hashexp:8);
lroym.DefineKey('ms_test_name','result_type', 'ms_test_sub_category', 'fast_ind', 'yearmonth');
lroym.DefineData('ms_test_name','result_type', 'ms_test_sub_category', 'fast_ind', 'yearmonth', 'count');
lroym.DefineDone();
/* msoc.lab_l3_dates */
declare hash l3dates(ordered:'a', hashexp:8);
l3dates.DefineKey('ms_test_name', 'result_type','orddt', 'labdt', 'resdt');
l3dates.DefineData('ms_test_name', 'result_type','orddt', 'labdt', 'resdt', 'count');
l3dates.DefineDone();
/* msoc.lab_l3_times */
declare hash l3times(ordered:'a', hashexp:8);
l3times.DefineKey('ms_test_name', 'result_type','labtm', 'restm');
l3times.DefineData('ms_test_name', 'result_type','labtm', 'restm', 'count');
l3times.DefineDone();
/*msoc.lab_l3_test_resultc_stats */
declare hash rescst(ordered: 'a', hashexp:8);
rescst.DefineKey("key4");
rescst.DefineData("ms_test_name", "ms_test_sub_category", "fast_ind", "specimen_source",
"pt_loc", "ms_result_c", "orig_result", "count");
rescst.DefineDone();
/* msoc.lab_l3_stat */
declare hash st(ordered: 'a', hashexp:8);
st.DefineKey("ms_test_name", "result_type", "stat");
st.DefineData("ms_test_name", "result_type", "stat", "count");
st.DefineDone();
/* msoc.lab_l3_result_loc */
declare hash resloc(ordered: 'a', hashexp:8);
resloc.DefineKey("ms_test_name", "result_type", "ms_test_sub_category", "fast_ind", "result_loc");
resloc.DefineData("ms_test_name", "result_type", "ms_test_sub_category", "fast_ind", "result_loc" ,"count");
resloc.DefineDone();
/* msoc.lab_l3_pt_loc */
declare hash ptloc(ordered: 'a', hashexp:8);
ptloc.DefineKey("ms_test_name", "result_type", "pt_loc");
ptloc.DefineData("ms_test_name", "result_type", "pt_loc" ,"count");
ptloc.DefineDone();
/* msoc.lab_l3_abn_ind */
declare hash abn(ordered: 'a', hashexp:8);
abn.DefineKey("ms_test_name", "result_type", "abn_ind");
abn.DefineData("ms_test_name", "result_type", "abn_ind" ,"count");
abn.DefineDone();
/* msoc.lab_l3_px_codetype */
declare hash pxtype(ordered: 'a', hashexp:8);
pxtype.DefineKey("ms_test_name", "result_type", "ms_test_sub_category", "fast_ind", "px_codetype");
pxtype.DefineData("ms_test_name", "result_type", "ms_test_sub_category", "fast_ind", "px_codetype" ,"count");
pxtype.DefineDone();
/* msoc.&module._l3_range */
declare hash rnge(ordered: 'a', hashexp:8);
rnge.DefineKey("key5");
rnge.DefineData("ms_test_name", "result_type", "ms_test_sub_category", "fast_ind", "specimen_source",
"modifier_low","norm_range_low","modifier_high","norm_range_high" ,"count");
rnge.DefineDone();
end;
do until(end);
set &qa_table end=end;
length key1 key2 key3 key4 key5 $16;
yearmonth=put(coalesce(lab_dt,result_dt,order_dt),yymmd.);
labdt=ifn(not missing(lab_dt),1,0);
resdt=ifn(not missing(result_dt),1,0);
orddt=ifn(not missing(order_dt),1,0);
labtm=ifn(not missing(lab_tm),1,0);
restm=ifn(not missing(result_tm),1,0);
/* Create compressed keys to reduce memory footprint */
key1=md5(cats(ms_test_name,result_type,ms_test_sub_category, fast_ind, specimen_source, loinc));
key2=md5(cats(ms_test_name,result_type,ms_test_sub_category, fast_ind, specimen_source,
orig_result_unit, std_result_unit, ms_result_unit));
key3=md5(cats(ms_test_name,result_type,ms_test_sub_category, fast_ind, specimen_source, loinc,
orig_result, ms_result_c, modifier, ms_result_n, ms_result_unit));
key4=md5(cats(ms_test_name, ms_test_sub_category, fast_ind, specimen_source, pt_loc, ms_result_c, orig_result));
key5=md5(cats(ms_test_name, result_type, ms_test_sub_category, fast_ind, specimen_source,
modifier_low,norm_range_low,modifier_high,norm_range_high));
if reccount.find() ^= 0 then count=0;
count+1;
reccount.replace();
if testunit.find() ^= 0 then count=0;
count+1;
testunit.replace();
if msres.find() ^= 0 then count=0;
count+1;
msres.replace();
if maptest.find() ^= 0 then n=0;
if upcase(ms_test_name) ^= 'UNMAPPED' then do;
n+1;
maptest.replace();
end;
if testym.find() ^= 0 then count=0;
count+1;
testym.replace();
if dtym.find() ^= 0 then n=0;
n+1;
dtym.replace();
if lropat.find() ^= 0 then count=0;
count+1;
lropat.replace();
if lroym.find() ^= 0 then count=0;
count+1;
lroym.replace();
if l3dates.find() ^= 0 then count=0;
count+1;
l3dates.replace();
if l3times.find() ^= 0 then count=0;
count+1;
l3times.replace();
if rescst.find() ^= 0 then count=0;
if not missing(ms_result_c) then do;
count+1;
rescst.replace();
end;
if st.find() ^= 0 then count=0;
count+1;
st.replace();
if resloc.find() ^= 0 then count=0;
count+1;
resloc.replace();
if ptloc.find() ^= 0 then count=0;
count+1;
ptloc.replace();
if abn.find() ^= 0 then count=0;
count+1;
abn.replace();
if pxtype.find() ^= 0 then count=0;
count+1;
pxtype.replace();
if rnge.find() ^= 0 then count=0;
if result_type = 'N' then do;
count+1;
rnge.replace();
end;
end;
attrib _all_ label='';
format count comma15.;
reccount.output(dataset:"msoc.&module._l2_record_count");
testunit.output(dataset:"msoc.&module._l2_test_units");
msres.output(dataset:"&module._l2_temp_ms_result");
maptest.output(dataset:"dplocal.&module._l2_mapped_tests");
testym.output(dataset:"dplocal.&module._testdates_ym");
dtym.output(dataset:"dplocal.temp_date_all");
lropat.output(dataset:"msoc.&module._l3_record_lrodt_ptloc_ym");
lroym.output(dataset:"msoc.&module._l3_record_lrodt_ym");
l3dates.output(dataset:"msoc.&module._l3_dates");
l3times.output(dataset:"msoc.&module._l3_times");
rescst.output(DATASET:"msoc.&module._l3_test_resultc_stats");
st.output(DATASET:"msoc.&module._l3_stat");
resloc.output(DATASET:"msoc.&module._l3_result_loc");
ptloc.output(DATASET:"msoc.&module._l3_pt_loc");
abn.output(DATASET:"msoc.&module._l3_abn_ind");
pxtype.output(DATASET:"msoc.&module._l3_ms_test_sub_category");
rnge.output(DATASET:"msoc.&module._l3_range");
stop;
run;
%macro get_qa_table ;
%if &do_partitions and not %eval(&module in &tabid_exc) %then %do ;
data t_view_1 /view=t_view_1 ;
set qadata.&&&module.table.: (keep=patid ms_test_name result_type order_dt lab_dt result_dt) ;
run;
data t_view_2 /view=t_view_2 ;
set qadata.&&&module.table.: (keep=patid ms_test_name result_type) ;
run;
%let qa_table_1 = t_view_1 ;
%let qa_table_2 = t_view_2 ;
%end ;
%else %do;
%let qa_table_1 = qadata.&&&module.table (keep=patid ms_test_name result_type order_dt lab_dt result_dt) ;
%let qa_table_2 = qadata.&&&module.table (keep=patid ms_test_name result_type) ;
%end;
%mend;
%get_qa_table ;
proc sql NOPRINT;
create table msoc.&module._l3_n_patid_test_ym as
select ms_test_name ,
result_type ,
put(coalesce(lab_dt,result_dt,order_dt),yymmd.) as YearMonth ,
count(unique patid) as count label="PatID Count" format=comma15.
from &qa_table_1
group by ms_test_name, result_type, calculated YearMonth ;
create table msoc.&module._l3_n_patid_test_y as
select ms_test_name ,
result_type ,
put(coalesce(lab_dt,result_dt,order_dt),year4.) as Year ,
count(unique patid) as count label="PatID Count" format=comma15.
from &qa_table_1
group by ms_test_name, result_type , calculated Year ;
create table msoc.&module._l3_n_patid_test as
select ms_test_name ,
result_type ,
count(unique patid) as count label="PatID Count" format=comma15.
from &qa_table_2
group by ms_test_name, result_type;
create table msoc.&module._l3_n_patid_enrolled_test_y as
select a.ms_test_name ,
a.result_type ,
put(coalesce(lab_dt,result_dt,order_dt),year4.) as Year ,
count(unique a.PatID) as count label="PatID Count" format=comma15.
from &qa_table_1 as a
right join dplocal._01_lab (keep=patid) as b
on a.patid=b.patid
group by a.ms_test_name, a.result_type, calculated Year ;
quit;
/*create list of all testnum for numeric tests that only have one result_type*/
proc sql noprint;
select quote(strip(ms_test_name)) into :testlist separated by ','
from (select count(distinct result_type) as typect, testnum, ms_test_name, result_type
from infolder.lkp_lab_test (where=(characterized='Y'))
group by testnum, ms_test_name
having calculated typect=1)
where result_type='N'
;
quit;
*Remove records for numeric tests with character results due to ranges (e.g. 10|50 MG/DL);
data msoc.&module._l2_ms_result msoc.&module._l2_ms_result_x;
set &module._l2_temp_ms_result;
if result_type = 'N' then output msoc.lab_l2_ms_result;
else if ms_test_name in (&testlist.) and result_type = 'C' then output msoc.lab_l2_ms_result_x;
else output msoc.lab_l2_ms_result;
run;
/**********************************************************************************/
/* Evalauate stamdard Level 2 non-aborting single table checks */
%level2;
/**********************************************************************************/
/* Create Level 2 flags for 3-variable cross-checks (checkid=23x) */
proc sort data = infolder.lkp_all_flags nodupkey out=_temp_flags_lab(keep=checkid varid variable1 variable2 variable3 variable4 dataset ms_test_name result_type lookup_table);
by checkid varid variable1 variable2 variable3 variable4 dataset lookup_table;
where checkid in ('230', '231', '232', '234', '235');
run;
proc sql noprint undo_policy=none;
create table _temp_flags_lab as
select distinct a.checkid, a.varid, a.variable1, a.variable2, a.variable3, a.variable4, a.dataset, a.lookup_table, b.testid
from _temp_flags_lab a
left join (select a.testid, a.ms_test_name, a.result_type
from infolder.lkp_lab_test(keep=testid ms_test_name result_type characterized where=(characterized='Y')) a
inner join _temp_flags_lab b
on a.ms_test_name=b.ms_test_name and a.result_type=b.result_type
where b.checkid='232') b
on a.ms_test_name=b.ms_test_name and b.result_type=b.result_type
where checkid in ('230', '231', '232', '234', '235');
quit;
data _null_;
set _temp_flags_lab;
length wherecond $23;
if checkid = '230' then wherecond=' is missing';
else if checkid = '231' then wherecond=' is not missing';
else if checkid = '234' then wherecond=' <0';
else if checkid = '235' then wherecond=' =0';
if checkid in ('230','231','234','235') then do;
call execute(cat("proc sql noprint; create table ",cats('flag_',checkid,'_',varid),' as
select flagid, count from (select ',cats('a.',variable2,',a.',variable3,',a.',variable1,
',b.flagid as flagid length=21,sum(a.count) as count format=comma15.'),
' from ', dataset,'(keep=',cat(variable2,variable3,variable1,
' count) a left join infolder.lkp_all_flags b on '),cats('a.',variable2,'=','b.',variable2), ' and ',
cats('a.',variable3,'=','b.',variable3), ' where b.checkid= "', checkid, '" and b.varid= "',
varid, '" and ', cat(variable1,wherecond), ' group by 1,2,3,4 having not missing(flagid));'));
end;
if checkid = '232' then do;
call execute(cat("proc sql noprint; create table ",cats('flag_',checkid,'_',varid),' as
select flagid, count from (select ',cat('a.',variable2,',a.',variable3,',a.',variable1,',
sum(a.count) as count format=comma15.,
case when ',cats('a.',variable1,'^=','b.',variable1),' then ',
upcase(cats('"','LAB_2_',varid,'_',testid,'_',checkid,'"')),' else " " end as FlagID length=21'),
' from (select c.*, d.testid from ', dataset,'(keep=',cat(variable2,variable3,variable1,' count) c inner join
infolder.lkp_lab_test(keep=ms_test_name result_type testid characterized where=(characterized="Y")) d on '),
cats('c.',variable2,'=','d.',variable2), ' and ',
cats('c.',variable3,'=','d.',variable3), ') a left join (select distinct testid,', cats(variable2, ",",
variable3, "," ,variable1),' from ',cats('infolder.',lookup_table),') b
on ', cat('a.', variable2, '=','b.',variable2,' and ', 'a.',variable3,'=','b.',variable3,' and ', 'a.',
variable1,'=','b.',variable1),' group by 1, 2, ',cats('a.',variable1),
' ,flagid having not missing(flagid));'));
end;
run;
proc sort data = _temp_flags_lab nodupkey out=unique_checkid(keep=checkid);
by checkid;
run;
data _null_;
set unique_checkid;
call execute(cats('data _null_;
if _n_=1 then do;
declare hash f(ordered:"a");
f.DefineKey("flagid");
f.DefineData("flagid","_count");
f.DefineDone();
end;
do until (end);
set work.flag_',checkid,'_: end=end;
if f.find() ^= 0 then _count=0;
_count=sum(_count,count);
f.replace();
end;
f.output(dataset:','"work.flag_',checkid,'(rename=(_count=count))");'));
call execute(cats('proc datasets lib=work nolist nowarn nodetails; delete flag_',checkid,'_: ;quit;'));
run;
data work.l2_flags_lab;
set work.flag_:;
run;
proc sql noprint;
create table dplocal.custom_l2_flags_&module. as
select b.flagid
, b.flagtype
, b.flag_descr
, b.flagyn
, b.abortyn
, a.count as count label=' ' format=comma15.
from l2_flags_&module. as a left join infolder.lkp_all_flags as b
on a.flagid=b.flagid
where b.flag_descr ne " "
;
quit;
data dplocal.l2_flags_&module;
set dplocal.l2_flags_&module
dplocal.custom_l2_flags_&module;
run;
proc datasets lib=dplocal nolist nowarn nodetails;
delete custom_l2_flags_&module;
quit;
proc datasets lib=work kill nolist nowarn nodetails;
quit;
/********** End of Level 2 ********************************************************/
/*---------------------------------------------------------------------------------------*/
/* START Level 3 Data */
/*---------------------------------------------------------------------------------------*/
%macro lab_date_dist;
%let datelist=lab result order;
%do j=1 %to 3;
%let date = %scan(&datelist,&j);
data _null_;
declare hash dt();
dt.DefineKey("&date._dt");
dt.DefineData("&date._dt","_n");
dt.DefineDone();
do until (end);
set dplocal.temp_date_all(keep=n &date._dt where=(not missing(&date._dt))) end=end;
if dt.find() ^= 0 then _n=0;
_n=sum(_n,n);
dt.replace();
end;
dt.output(dataset:"dplocal.temp_date(rename=(_n=n))");
run;
%let datenobs=0;
%if %sysfunc(exist(dplocal.temp_date)) %then %do;
data _null_;
if 0 then set dplocal.temp_date nobs=datenobs;
call symputx('datenobs',datenobs);
run;
%end;
%if &datenobs. gt 0 %then %do;
%date_percentiles (libin=dplocal, dsin=temp_date, libout=dplocal, dsout=temp_date_dist_&module._&j., vars=&date._dt);
%end;
proc datasets lib=dplocal nolist nowarn nodetails; delete temp_date; quit;
%end;
data _null_;
if _n_ = 1 then do;
declare hash l3dt();
l3dt.DefineKey("ms_test_name","result_type","YearMonth_lab","YearMonth_result","YearMonth_order");
l3dt.DefineData("ms_test_name","result_type","YearMonth_lab","YearMonth_result","YearMonth_order","count");
l3dt.DefineDone();
end;
do until (end);
set dplocal.temp_date_all(keep=ms_test_name result_type order_dt lab_dt result_dt n) end=end;
YearMonth_lab=put(lab_dt,yymmd.);
YearMonth_result=put(result_dt,yymmd.);
YearMonth_order=put(order_dt,yymmd.);
if l3dt.find() ^= 0 then count=0;
count=sum(count,n);
l3dt.replace();
end;
l3dt.output(dataset:"msoc.lab_l3_dates_ym");
run;
%mend;
%lab_date_dist;
%set_ds (libin=dplocal, dsin_prefix=temp_date_dist_&module., libout=dplocal, dsout=date_dist_&module.);
proc datasets lib=dplocal nolist;
delete temp_date_all;
quit;
/**********************************************************************************/
/* Create msoc.lab_l3_n_unmap_loinc_all as one way frequencies of unmapped LOINCS */
/* Create msoc.lab_l3_n_unmap_loinc_ym as unmapped LOINC frequencies stratified by year-month (top 5000 LOINCS) */
/* Create msoc.lab_l3_n_unmap_loinc_agegrp_sex as unmapped LOINC requencies stratified by agegroup and sex (top 5000 LOINCS) */
%macro get_qa_table ;
%if &do_partitions and not %eval(&module in &tabid_exc) %then %do ;
data t_view /view=t_view ;
set qadata.&&&module.table.:(keep=LOINC PatID ms_test_name) ;
run;
%let qa_table = t_view ;
%end ;
%else %do;
%let qa_table = qadata.&&&module.table(keep=LOINC PatID ms_test_name) ;
%end;
%mend;
%get_qa_table ;
/*msoc.lab_l3_n_unmap_loinc_all*/
proc sql noprint;
create table msoc.&module._l3_n_unmap_loinc_all as
select LOINC
,count(distinct PatID) as PatID_count format=comma15.
,count(LOINC) as Count format=comma15.
from &qa_table
where ms_test_name='UNMAPPED'
group by LOINC
order by Count descending
;
quit;
data loinc5000;
set msoc.&module._l3_n_unmap_loinc_all(obs=5000);
run;
%macro get_qa_table ;
%if &do_partitions and not %eval(&module in &tabid_exc) %then %do ;
data t_view /view=t_view ;
set qadata.&&&module.table:(keep=patid loinc lab_dt result_dt order_dt) ;
run;
%let qa_table = t_view ;
%end ;
%else %do;
%let qa_table = qadata.&&&module.table(keep=patid loinc lab_dt result_dt order_dt) ;
%end;
%mend;
%get_qa_table ;
proc sql noprint;
create table msoc.&module._l3_n_unmap_loinc_ym as
select put(coalesce(lab_dt,result_dt,order_dt),yymmd.) as YearMonth
,LOINC
,count(distinct patid) as PatID_count format=comma15.
,count(*) as count format=comma15.
from &qa_table
where loinc in (select loinc from loinc5000)
group by calculated YearMonth, LOINC;
quit;
*Merge &demtable and &labtable to calculate age/agegroups;
%macro get_qa_table ;
%if &do_partitions and not %eval(&module in &tabid_exc) %then %do ;
data t_view_1 /view=t_view_1 ;
set qadata.&DemTable.: (keep = PatID sex birth_date) ;
run;
data t_view_2 /view=t_view_2 ;
set qadata.&&&module.table.: (keep = LOINC PatID lab_dt result_dt order_dt) ;
run;
%let qa_table_1 = t_view_1 ;
%let qa_table_2 = t_view_2 ;
%end ;
%else %do;
%let qa_table_1 = qadata.&DemTable. (keep = PatID sex birth_date) ;
%let qa_table_2 = qadata.&&&module.table (keep = LOINC PatID lab_dt result_dt order_dt) ;
%end;
%mend;
%get_qa_table ;
proc sql noprint;
create table msoc.&module._l3_n_unmap_loinc_agegrp_sex as
select sex,
AgeGroup,
LOINC,
count(distinct PatID) as PatID_count format=comma15.,
count(LOINC) as Count format=comma15.
from (select a.PatID
,a.sex
,b.LOINC
,coalesce(lab_dt,result_dt,order_dt) as TestDate
,floor((intck('month',a.birth_date, calculated testdate)-(day(calculated testdate)<day(a.birth_date)))/12) as age_years
,case when calculated age_years = . then 'Missing'
when calculated age_years between 0 and 17 then '0-17 yrs'
when calculated age_years between 18 and 39 then '18-39 yrs'
when calculated age_years between 40 and 64 then '40-64 yrs'
else '65+ yrs'
end as AgeGroup
from &qa_table_1 as a
right join &qa_table_2 as b
on a.PatID=b.PatID
where b.LOINC in (select loinc from loinc5000))
group by sex, AgeGroup, LOINC
;
quit;
/**********************************************************************************/
/* Create msoc.lab_l3_test_resultn_stats_ms as descriptive statistics of MS_result_n by test */
/* Create msoc.lab_l3_test_resultn_stats_orig as descriptive statistics of orig_result_n by test */
%let pn= ;
%macro get_qa_pn ;
%if &do_partitions and not %eval(&module in &tabid_exc) %then %let pn=: ; %else %let pn= ;
%mend;
%get_qa_pn ;
data _lab_temp;
set qadata.&&&module.table&pn.(keep=MS_result_n orig_result
MS_test_name result_type MS_test_sub_category fast_ind specimen_source pt_loc
modifier orig_result_unit std_result_unit MS_result_unit);
if result_type = 'N' then orig_result_n=input(strip(orig_result),8.);
run;
%macro mean1 (var=, out=);
proc means data=_lab_temp
(keep=MS_result_n %if &var.=orig_result_n %then %do; &var. %end;
MS_test_name result_type MS_test_sub_category fast_ind specimen_source pt_loc
modifier orig_result_unit std_result_unit MS_result_unit) noprint nway;
var &var.;
class MS_test_name result_type MS_test_sub_category fast_ind specimen_source pt_loc modifier MS_result_unit std_result_unit orig_result_unit / missing;
where MS_result_n ne . ;
output out=temp (drop=_type_ rename=_freq_=count)
mean=Mean stddev=Std min=Min p5=P5 p25=P25 median=Median mode=Mode p75=P75 P95=P95 max=Max /keeplen;
run;
data msoc.&module._l3_test_resultn_stats_&out.;
retain MS_test_name Result_type MS_Test_Sub_Category Fast_Ind Specimen_Source Pt_Loc Orig_Result_Unit Std_Result_unit MS_Result_Unit;
set temp;
format mean median std 10.2 count comma15.;
run;
proc datasets lib=work nolist nowarn nodetails; delete temp; quit;
%mend;
%mean1 (var=ms_result_n, out=ms);
%mean1 (var=orig_result_n, out=orig);
/**********************************************************************************/
/* MS_Result_N by Range category */
proc sort data=infolder.lkp_lab_result_ranges out=work.range;
by fmtname;
run;
proc format cntlin=range;
run;
%macro ranges (var=, var_short=);
proc sql noprint;
select distinct strip(fmtname),
count(distinct fmtname)
into :fmtlist separated by " ",
:nfmt trimmed
from work.range
;
quit;
%do i=1 %to &nfmt.;
%let fmt=%scan(&fmtlist., &i.);
%if &do_partitions and not %eval(&module in &tabid_exc) %then %do ;
data t_view /view=t_view ;
set qadata.&&&module.table.: (keep=ms_test_name result_type ms_test_sub_category
fast_ind &var. ms_result_n ms_result_unit modifier) ;
where result_type='N' and modifier='EQ' and ms_result_n ne . and
compress(ms_test_name||ms_test_sub_category||fast_ind||ms_result_unit,'/ ')="&fmt." ;
run;
%let qa_table = t_view ;
%end ;
%else %do;
%let qa_table = qadata.&&&module.table (keep=ms_test_name result_type ms_test_sub_category
fast_ind &var. ms_result_n ms_result_unit modifier)
where result_type='N' and modifier='EQ' and ms_result_n ne . and
compress(ms_test_name||ms_test_sub_category||fast_ind||ms_result_unit,'/ ')="&fmt." ;
%end;
proc sql noprint;
create table work.temp as
select ms_test_name
, ms_test_sub_category
, fast_ind
, &var.
, ms_result_unit
, put(ms_result_n, &fmt..) as ms_result_range length=25
, count(*) as count format=comma15.
, min(ms_result_n) as OrderVar
from &qa_table
group by ms_test_name, ms_test_sub_category, fast_ind, &var., ms_result_unit, ms_result_range
order by ms_test_name, ms_test_sub_category, fast_ind, &var., ms_result_unit, ordervar
;
quit;
%if %unquote(&sqlobs.) ne 0 %then %do;
proc sql noprint;
select sum(count), substr(ms_test_name, 1, 6) into :ntest trimmed, :test trimmed
from work.temp
;
create table msoc.lab_l3_cat_resultn_&var_short._&test. (drop=ordervar) as
select *
from work.temp
;
quit;
%end;
%end;
proc datasets lib=work nolist;
delete temp;
quit;
%mend;
%ranges (var=pt_loc, var_short=ptloc);
%ranges (var=specimen_source, var_short=ss);
/********** End of Level 3 ********************************************************/
/* Clean up temporary or empty datasets and end module */
proc datasets kill lib=work memtype=data nolist nodetails nowarn;
quit;
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;
* END of scdm_data_qa_review-labs.sas ;
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;