Compare observations within groups and find out the missing observation

[VikrantSawatkar]

Hi Everyone,

I have a data which is as follows. I want to check the result mentioned for patient let's say 10001 -> Visit 1 -> Test No 1 -> Result with his every visit's Test no. 1 -> Result and if the Test is not performed for that particular visit or visit is missing then it should be flagged as a Missing Test or Missing Visit.

E.g. - Patient 10001 -> Visit 1 -> Test No 1 -> Result with Patient 10001 -> Visit 2 -> Test No 1- > Result. Also, it should be checked with Visit 3 and so on (till the last visit's are present for the patient). The Patient 10001 has done 4 Tests in Visit 1 and 3 Tests in Visit 2 so his 4th Test is not mentioned or not performed then it should be flagged as 'Test 4 is absent' for the Visit 2. Also, if the Visit is not mentioned for the patient then it also should be flagged.

Could you please help me out. Thank you very much in advance.

Kind Regards,

Vikrant Sawatkar

[Ksharp]

You didn't post the result yet.

CODE NOT TESTED.

proc sql;
select a.*,b.Result, missing(b.Result) as flag
    from
(
select * from
(select distinct PatientNumber from have),
(select distinct VisitNo from have),
(select distinct TestNo from have)
) as a
left join have as b
 on a.PatientNumber =b.PatientNumber and
      a.VisitNo =b.VisitNo  and
      a.TestNo =b.TestNo ;
quit;

[Quentin]

Hi,

Are you saying you expect every patient to have records for all 3 visits, and all 4 tests done at each visit? 

One way to approach this sort of problem is to build a grid of all data you expect:

data expect;
  do patient=10001 to 10004;
    do visit=1 to 3;
      do test=1 to 4;
        output;
      end;
    end;
  end;
run;

Then you can merge EXPECT against your HAVE data to identify any missing records, something like:

data want;
  merge expect (in=a)
        have   (in=b)
  ;
  by patient visit test;
  if a=0 then put "ERROR: record in have not expected " (patient visit test)(=);
  if b=0 then MissingRecord=1;
run;