members who have changed their Emailable Status only once. Means from No to Yes and Yes to No and not from y to y and n to n. and wanted to calculate distinct new mbr id as Active members on the basis of it

[ManoharNath]

members who  have changed their Emailable  Status only once.

Means from No to Yes and Yes to No and not from y to y and n to n.

and wanted to calculate distinct new mbr id as Active members on the basis of it and

for refrence please see the attached data file.

[RW9 · Accepted solution]

Its really easier to put in a test datastep with some values and required output.  From what I can gather something like:

proc sql;

     create table WANT as

     select     distinct(A.MBR_ID)

     from       HAVE A

     where     exists(select distinct MBR_ID from HAVE where RESULT="YES")

          and    exists(select distinct MBR_ID from HAVE where RESULT="NO");

quit;