Help using Base SAS procedures

Editcheck query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

Editcheck query

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


Accepted Solutions
Solution
‎03-10-2014 03:09 PM
Super User
Posts: 19,772

Re: Editcheck query

Posted in reply to rakeshvvv

Its preferable if you start with this Smiley Happy

Something like the following may work (untested).

proc freq data=have noprint;

table patno*timepoint/out=check(where=(count=0)) sparse;

run;

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: Editcheck query

Posted in reply to rakeshvvv

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;

Frequent Contributor
Posts: 145

Re: Editcheck query

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.

Super Contributor
Posts: 578

Re: Editcheck query

Posted in reply to rakeshvvv

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;

Super User
Posts: 19,772

Re: Editcheck query

Posted in reply to rakeshvvv

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;

Frequent Contributor
Posts: 145

Re: Editcheck query

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)

Solution
‎03-10-2014 03:09 PM
Super User
Posts: 19,772

Re: Editcheck query

Posted in reply to rakeshvvv

Its preferable if you start with this Smiley Happy

Something like the following may work (untested).

proc freq data=have noprint;

table patno*timepoint/out=check(where=(count=0)) sparse;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 234 views
  • 3 likes
  • 3 in conversation