DATA Step, Macro, Functions and more

missing visits

Reply
Occasional Contributor
Posts: 10

missing visits

Hello there,

I have a dataset like

subjid visit

101      1

101      2

101      3

101      4

101      5

101      6

102      1

102      5

102      6

I would like to flag the subject like 102 that has consecutive 3 visit missing and delete that subject from my study. Can someone share the logic of how to do that? Thanks for all your time and help.

Respected Advisor
Posts: 3,124

Re: missing visits

Given the assumption that your data has already been sorted by subjid, visit, a classic 2XDOW will do: (of course there are other approaches as well)

data have;

input subjid visit;

cards;

101      1

101      2

101      3

101      4

101      5

101      6

102      1

102      5

102      6

;

data want;

  do until(last.subjid);

    set have;

   by subjid;

    if dif(visit) >= 3 then _flag=1;

  end;

  do until(last.subjid);

    set have;

   by subjid;

    if  _flag ne 1 then output;

  end;

drop _flag;

run;

Haikuo

Super User
Posts: 17,829

Re: missing visits

What do you want the output to look like?

Occasional Contributor
Posts: 10

Re: missing visits

I would only like to output only those subjects that has come to all six visits without missing consecutive 3 in a row.

Super User
Posts: 17,829

Re: missing visits

Can a person have more than one visit? e.g 2 visit 2's? If not then a quick SQL step would work:

proc sql;

create table want as

select *

from have

group by subjid

having count(subjid)=6;

quit;

Valued Guide
Posts: 797

Re: missing visits

Here's a single data step technique that works without the double DOW.  IWhenever a new subjid starts, it compares the id to the subjid 5 records ahead.  If they match, keep the subjid.  This assumes that there cannot be more than one record per visit:

data have;
input subjid visit @@;
datalines;
101      1        101      2              101      3           101      4           101      5             101      6
102      1        102      5              102      6
run;

data want (drop=_Smiley Happy;
  set have ;
  by subjid;
  if eod=0 then set have (firstobs=6 keep=subjid rename=(subjid=_subjid6)) end=eod;
  else _subjid6=.;
  retain _flag;
  if first.subjid=1 then _flag=(subjid=_subjid6);
  if _flag=1;
run;

Ask a Question
Discussion stats
  • 5 replies
  • 259 views
  • 3 likes
  • 4 in conversation