## 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

Solved
Occasional Contributor
Posts: 10

# 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

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.

Accepted Solutions
Solution
‎11-25-2015 05:17 AM
Super User
Posts: 9,599

## Re: 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

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;

All Replies
Solution
‎11-25-2015 05:17 AM
Super User
Posts: 9,599

## Re: 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

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;

🔒 This topic is solved and locked.