topic Re: Editcheck query in SAS Procedures

Editcheck query

rakeshvvv — Mon, 10 Mar 2014 14:01:58 GMT

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

Re: Editcheck query

DBailey — Mon, 10 Mar 2014 14:39:11 GMT

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

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;

Re: Editcheck query

rakeshvvv — Mon, 10 Mar 2014 15:30:34 GMT

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.

Re: Editcheck query

DBailey — Mon, 10 Mar 2014 15:50:20 GMT

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

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;

Re: Editcheck query

Reeza — Mon, 10 Mar 2014 15:50:59 GMT

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;

Re: Editcheck query

rakeshvvv — Mon, 10 Mar 2014 18:03:29 GMT

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)

Re: Editcheck query

Reeza — Mon, 10 Mar 2014 19:09:50 GMT

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;