Editcheck query

[rakeshvvv]

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

[DBailey]

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;

[rakeshvvv]

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.

[DBailey]

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;

[Reeza]

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;

[rakeshvvv]

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)

[Reeza · Accepted solution]

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;