I would like output query for the following specification,
On Day 1(Visit=2) all patients who completed treatment should have ECG at 7 time points: predose and 1, 1.5, 4, 8, 12, and 24 hours post. If they are missing any of these, the missing time points have to be reported.
Ex:
In ‘X’ dataset we have two variables PATNO and Visit.
So we have to select all the patno with visit equal 2 From ‘X’ and this patients present in the ecg(Ecg has variables PATNO ECG_TIMEPOINT) should have all the 7 time points collected IN ECG_TIMEPOINT. If any one of these time points are missing we should out put the patient with time point.
Thanks
Rocky
Its preferable if you start with this
Something like the following may work (untested).
proc freq data=have noprint;
table patno*timepoint/out=check(where=(count=0)) sparse;
run;
It would definitely depend on your data sources...but...maybe this will help get you started.
proc sql;
create table want as
select
x.patno,
predose.egc_timepoint as Predose_Timepoint,
v1.egc_timepoint as V1_Timepoint,
v2.egc_timepoint as V2_Timepoint
from
x
left join ecg predose
on x.patno=predose.patno
and predose.egc_timepoint='PREDOSE'
left join ecg v1
on x.patno=v1.patno
and v1.ecg_timepoint=1
left join ecg v2
on x.patno=v2.patno
and v2.egc_timepoint=1.5
where
x.visit=2;
quit;
I will try to make it simple for our understanding. We have dataset (EG) with two variables patno, timepoint. For every patno, we are expected to have 7 time point values (a,b,c,d,e,f,g). If anyone of those time points is missing, we would require to output patno with missing time points for every patno as discrepancy. Hope it helps.
forgot to add some criteria:
proc sql;
create table want as
select
x.patno,
predose.egc_timepoint as Predose_Timepoint,
v1.egc_timepoint as V1_Timepoint,
v2.egc_timepoint as V2_Timepoint
from
x
left join ecg predose
on x.patno=predose.patno
and predose.egc_timepoint='PREDOSE'
left join ecg v1
on x.patno=v1.patno
and v1.ecg_timepoint=1
left join ecg v2
on x.patno=v2.patno
and v2.egc_timepoint=1.5
where
x.visit=2
and (
Predose.patno is null
or v1.patno is null
or v2.patno is null
)
;
quit;
Something like the following should work, assuming you can't have multiple visits for a time point.
The subquery selects patient numbers where the count is not equal to 7 and then the query selects all the data for those patients.
proc sql;
create table want as
select * from have
where patno in (select patno from have group by patno having count(patno) ne 7);
quit;
Hi Reeza,
The query worked partially but would like to output the discrepancies. For all patients, we expect the time point variable to have all values a,b,c,d,e,f,g(no multiple values). So the summation logic of 7 is correct. But I would like to output discrepancy like patno and missing of any of a,b,c,d,e,f,g.
PATNO TIMEPOINT
001 A
001 B
001 C
001 D
001 E
001 F
001 G
002 A
002 B
002 C
002 D
002 F
002 G
003 A
003 B
003 C
003 F
003 G
For example, for the above dataset we expect the output of following.
Patno timepoint
002 E(E was meant to be present but was missing.so we needed to output this discrepancy )
003 D(D was meant to be present but was missing.so we needed to output this discrepancy)
003 E(E was meant to be present but was missing.so we needed to output this discrepancy)
Its preferable if you start with this
Something like the following may work (untested).
proc freq data=have noprint;
table patno*timepoint/out=check(where=(count=0)) sparse;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.