DATA Step, Macro, Functions and more

Retain statement vs. first.ob

Reply
Frequent Contributor
Posts: 106

Retain statement vs. first.ob

I have a dataset with multiple observations per patient. All patients need to have a certain number of visits. I want to find the patients that are missing visits. If they have not had that visit, there is no observation for that patient for that visit. How do I find which patients are missing those visits?

Respected Advisor
Posts: 3,799

Re: Retain statement vs. first.ob

Posted in reply to sarahsasuser

When you say "certain number of visits" do you mean scheduled visit or just any visits as long as the number is sufficient.  We like example data too.

Frequent Contributor
Posts: 106

Re: Retain statement vs. first.ob

Posted in reply to data_null__

The study requires 10 visits, but we haven't reached the date where any patient would have all 10 visits yet. Currently we are at visit 4. So I want to see how many patients are missing  any of the first 4 visits. I've created a calculated variable that is the visit due date, as I'd also like to see who had a late visit.

Patient ID   Visit#   Visit date  Visit due date

123               1          1/1/13    1/1/13

123               2          2/1/13     2/1/13

123               3          3/1/13     3/1/13

124               1           2/22/13   2/12/13

124               3          4/24/13    2/24/13

125               1          1/1/13    1/1/13         

125               2          2/1/13     2/1/13  

125               4          4/15/13    4/1/13     

Respected Advisor
Posts: 3,799

Re: Retain statement vs. first.ob

Posted in reply to sarahsasuser

I might do something like this.  Build a visit schedule for each subject and join it with the actual data then you can count the holes and see if the schedule visit times are close.

data visit;
   infile cards dsd;
  
input patno visit date:mmddyy.;
  
format date date.;
  
cards;
123,1,1/1/13
123,2,2/1/13
123,3,3/1/13
124,1,2/22/13
124,3,4/24/13
125,1,1/1/13
125,2,2/1/13
125,4,4/15/13
;;;;
   run;
data schedule;
   set visit;
   where visit eq 1;
  
do visit=1 to 4;
      edate = intnx(
'MONTH',date,visit-1,'Sameday');
      output;
     
end;
  
format edate date.;
  
drop date;
   run;
data visit2;
   merge schedule(in=in1) visit(in=in2);
   by patno visit;
   if not in2 then mvisit=1;
  
if not missing(date) then dif = date - edate;
   run;

Super User
Posts: 5,497

Re: Retain statement vs. first.ob

Posted in reply to sarahsasuser

Another approach:

data want;

   set have;

   by patient_id visit;

   if first.patient_id then expected_visit=1;

   else expected_visit + 1;

   if (visit ne expected_visit) or (last.patient_id and visit < 4) ;

run;

This will point out additional problems that might exist in the data, such as a patient having two observations with visit=3.

Good luck.

Respected Advisor
Posts: 4,919

Re: Retain statement vs. first.ob

Posted in reply to sarahsasuser

How about :

data patientData;
informat visitDate visitDueDate mmddyy.;
format visitDate visitDueDate ddmmyy10.;
input PatientID   VisitNo   VisitDate  VisitDueDate;
datalines;
123               1          1/1/13    1/1/13
123               2          2/1/13     2/1/13
123               3          3/1/13     3/1/13
124               1           2/22/13   2/12/13
124               3          4/24/13    2/24/13
125               1          1/1/13    1/1/13         
125               2          2/1/13     2/1/13  
125               4          4/15/13    4/1/13     
;

%let totalVisits=10;

data visits; do visitNo=1 to &totalVisits; output; end; run;

proc sql;
create table expandedPatientData as
select P.patientId, V.visitNo, 
case
  when visitDate is missing then "- "
  when visitDate > visitDueDate then put(intck("DAY",visitDueDate, visitDate), 3.)
  else "  "
  end as code length = 3
from
(select distinct PatientId from patientData) as P cross join
visits as V left join
patientData as X on P.patientId=X.patientId and V.visitNo=X.visitNo
order by patientId, visitNo;
quit;

proc transpose data=expandedPatientData out=patientDataList prefix=V;
by patientId;
id visitNo;
var code;
run;

proc print data=patientDataList;
var v1-v&totalVisits;
ID patientId;
run;

        Patient
           ID      V1    V2    V3    V4    V5    V6    V7    V8    V9    V10

          123                        -     -     -     -     -     -      -
          124      10    -     59    -     -     -     -     -     -      -
          125                  -     14    -     -     -     -     -      -

PG

PG
Respected Advisor
Posts: 4,919

Re: Retain statement vs. first.ob

Posted in reply to sarahsasuser

Try this :

%let expectedNbVisits=3;

Proc sql;

create table missingVisits as

select patientId, count(visitDate) as nbVisits

from patientData

group by patientId

having count(visitDate) < &expectedNbVisits;

select * from missingVisits;

quit;

PG

PG
Super User
Posts: 10,020

Re: Retain statement vs. first.ob

Posted in reply to sarahsasuser

Or SQL.

proc sql;
 select count(distinct pid) as How_Many
  from (select * from have group by pid having count(distinct pid) lt 4);
quit;

Ksharp

Ask a Question
Discussion stats
  • 7 replies
  • 327 views
  • 0 likes
  • 5 in conversation