## Retain statement vs. first.ob

Frequent Contributor
Posts: 110

# 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?

Posts: 3,852

## Re: Retain statement vs. first.ob

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: 110

## Re: Retain statement vs. first.ob

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

Posts: 3,852

## Re: Retain statement vs. first.ob

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: 6,762

## Re: Retain statement vs. first.ob

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.

Posts: 5,526

## Re: Retain statement vs. first.ob

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
Posts: 5,526

## Re: Retain statement vs. first.ob

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,770

## Re: Retain statement vs. first.ob

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

Discussion stats
• 7 replies
• 342 views
• 0 likes
• 5 in conversation