Identify all records having unexpected change in a data element value

[DeepakSwain]

Hi there,

For your kind information, I am trying to identify all records of a person who is having changes in the value of a particular data element which is usually not expected in general. Here ID 102 is having LHIN value changed. 

data have ;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
101 1 01JAN2017 90
101 1 03JAN2017 80
101 1 05JAN2017 70 
102 2 01JAN2017 90
102 2 03JAN2017 70
102 3 05JAN2017 70
;
run;



data want;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
102 2 01JAN2017 90
102 2 03JAN2017 70
102 3 05JAN2017 70
;
run;

Thank you in advance for your kind reply.
Regards,

[Reeza]

So you're looking for ID's where the LHIN changes for the person?

---

@DeepakSwain wrote:

Hi there,

For your kind information, I am trying to identify all records of a person who is having changes in the value of a particular data element which is usually not expected in general. Here ID 102 is having LHIN value changed. 

data have ;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
101 1 01JAN2017 90
101 1 03JAN2017 80
101 1 05JAN2017 70 
102 2 01JAN2017 90
102 2 03JAN2017 70
102 3 05JAN2017 70
;
run;



data want;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
102 2 01JAN2017 90
102 2 03JAN2017 70
102 3 05JAN2017 70
;
run;

Thank you in advance for your kind reply.
Regards,

---

[ballardw · Accepted solution]

Perhaps

proc sql;
   create table want as
   select b.*
   from (select id 
             from (select distinct id,lhin from have)
             group by id
             having count(*)>1
         ) as a
         left join 
         have as b
         on a.id=b.id
   ;
quit;

[DeepakSwain]

Is it possible to identify the record having the least used LHIN.
e.g. 102 3 05JAN2017 70