Flag rows with same values on x and y but different value on z

Question

In SAS Enterprise, I am trying to identify any person with the same value on x and y but a different value on z (z are supposed to be the same but some people have more than one value of z).

One way to do this would be to sort on x, y, z - but can anyone suggest a way to flag instances of z within x and y that differ?

Answer 1

I'm not entirely clear with what you require, some sample data would be helpful to make the question clearer. However, I've made an attempt at answering anyway. So, assuming your input table is called my_table, the following code creates two datasets giving you the results you need.

/* The flagged table contains just those x,y groups with differing z */
proc sql;
  create table flagged as
  select 
  x,
  y
  from my_table
  group by x,y
  having count(distinct z) > 1;
quit;

/* Join the flagged table back to the original table to get the z values back */
proc sql;
  create table flagged_all as
  select
  f.x,
  f.y,
  a.z
  from 
  my_table as a
  inner join
  flagged f
  on a.x = f.x
  and a.y = f.y;
quit;

EDIT: Or you can accomplish this using a sub-select:

proc sql;
  create table flagged_all as
  select
  f.x,
  f.y,
  a.z
  from 
  my_table as a
  inner join
  (select 
  x,
  y
  from my_table
  group by x,y
  having count(distinct z) > 1) f
  on a.x = f.x
  and a.y = f.y;
quit;

However, this reduces the readability of the code. To get the same functionality, you could replace the flagged table in the first version with a view (create view flagged as...).