BookmarkSubscribeRSS Feed
Obsidian | Level 7

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


Super User

You didn't post the result yet.



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 ;
Super User


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;


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;
BASUG is hosting free webinars Next up: Don Henderson presenting on using hash functions (not hash tables!) to segment data on June 12. Register now at the Boston Area SAS Users Group event page:


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3 in conversation