libname adp 'Z:\Kite_Pharma\Biomarker\ZUMA-2\adams';
libname sdp 'Z:\Kite_Pharma\Biomarker\ZUMA-2\sdtms';
libname rdp 'Z:\Kite_Pharma\Biomarker\ZUMA-2\raw';

* steps

count and unique are used for counting obs and patients, no group by usubjid unless part of CASE WHEN since creating only one overall summary record
conditions to get overall counts
each variable is created from separate datasets with conditions
include using convention where dataset # = varibale #
adams (detail and summary, a1-a6), sdtms (s1-s3) and raw (r1-r3)
order - obs is total obs, ptc is unique patient count with or without condition, duplicates based on key variables, codelist
link by b.combo = a1-a6.combo
;

 
options mprint symbolgen mlogic;
%macro _obs(keyvr=subjid, stagen=r1, stagel=rdp, stageds=pcr);
proc sql;
create table &stagen as
select 'check' as combo, count(&keyvr) as &stagen._obs label="&stagel: &stageds total obs" from &stagel..&stageds; quit;
%mend _obs;

* ptc is unique patient count with or without condition;
%macro _ptc(keyvr=subjid, stagen=r2, stagel=rdp, stageds=pcr, cond=%str(where lbtestcd = 'QPPB'), clabel=QPPB);
proc sql;
create table &stagen as
select 'check' as combo, count(unique &keyvr) as &stagen._ptc label="&stagel: &stageds &clabel patient count"
  from &stagel..&stageds &cond; quit;
%mend _ptc;

* duplicates based on key variables;
%macro _dup(keyvr=subjid, grpvr=subjid, stagen=r3, stagel=rdp, stageds=pcr, cond=%str(where paramcd = 'nCART' and avisit = 'BASELINE'));
proc sql;
create table &stagen as
select unique 'check' as combo,
  case when count(&keyvr) = 1 then "No Dup "
  else 'Yes Dup' end as &stagen._dup label="&stagel: &stageds duplicate patient count"
  from &stagel..&stageds &cond group by &grpvr; quit;
%mend _dup;

* adpcr;
%_obs(keyvr=usubjid, stagen=a1, stagel=adp, stageds=adpcr);
%_ptc(keyvr=usubjid, stagen=a2, stagel=adp, stageds=adpcr, cond=%str(where paramcd = 'nCART'), clabel=nCART);
%_dup(keyvr=usubjid, grpvr=usubjid, stagen=a3, stagel=adp, stageds=adpcr, cond=%str(where paramcd = 'nCART' and avisit = 'BASELINE'));

* adpcrs;
%_obs(keyvr=usubjid, stagen=a4, stagel=adp, stageds=adpcrs);
%_ptc(keyvr=usubjid, stagen=a5, stagel=adp, stageds=adpcrs, cond=%str(where paramcd = 'nCART'), clabel=nCART);
%_dup(keyvr=usubjid, grpvr=usubjid, stagen=a6, stagel=adp, stageds=adpcrs, cond=%str(where paramcd = 'nCART'));

* pc;
%_obs(keyvr=usubjid, stagen=s1, stagel=sdp, stageds=pc);
%_ptc(keyvr=usubjid, stagen=s2, stagel=sdp, stageds=pc, cond=%str(where pctestcd = 'QPPB'), clabel=nCART);
%_dup(keyvr=usubjid, grpvr=usubjid, stagen=s3, stagel=sdp, stageds=pc, cond=%str(where pctestcd = 'QPPB' and visit eqt 'Screening'));

* pcr;
%_obs(keyvr=subjid, stagen=r1, stagel=rdp, stageds=pcr);
%_ptc(keyvr=subjid, stagen=r2, stagel=rdp, stageds=pcr, cond=%str(where lbtestcd = 'QPPB'), clabel=nCART);
%_dup(keyvr=subjid, grpvr=subjid, stagen=r3, stagel=rdp, stageds=pcr, cond=%str(where lbtestcd = 'QPPB' and visit eqt 'Enrollment'));

* combine all related variables;
proc sql;
create table ncartck (drop = combo) as
 select unique 'check' as combo
 , a1_obs, a2_ptc, a3_dup
, a4_obs, a5_ptc, a6_dup
, s1_obs, s2_ptc, s3_dup
, r1_obs, r2_ptc, r3_dup
from a1 left join a2 on a1.combo=a2.combo left join a3 on a1.combo=a3.combo
         left join a4 on a1.combo=a4.combo left join a5 on a1.combo=a5.combo left join a6 on a1.combo=a6.combo
         left join s1 on a1.combo=s1.combo left join s2 on a1.combo=s2.combo left join s3 on a1.combo=s3.combo
         left join r1 on a1.combo=r1.combo left join r2 on a1.combo=r2.combo left join r3 on a1.combo=r3.combo
;
quit;