Count distinct numbers and excluding records with Set Analysis

[mlattemann]

Dear all,

I am still new to the set analysis and this syntax really drives me crazy 😉

from the following table (extract from qvd-file) I want to count all distinct subjids which are no screen failures:

SUBJID	ENSTATUS	TERMINATION_TYPE
X004-001	terminated	Premature (screen failure)
X004-002	terminated	Premature (screen failure)
X004-003	terminated	Premature (screen failure)
X004-004	terminated	Premature (screen failure)
X004-005	enrolled
X004-006	enrolled
X004-007	terminated	Premature (screen failure)
X004-008	terminated	Premature (screen failure)
X004-009	terminated	Premature (screen failure)
X004-010	terminated	Premature (screen failure)
X009-001	enrolled
X009-002	enrolled
X009-003	enrolled
X009-004	terminated	Premature (screen failure)
X009-005	enrolled
X009-006	enrolled
X009-007	enrolled
X009-008	enrolled
X009-009	terminated	Premature (screen failure)

Counting all premature termination works:

Count({<TERMINATION_TYPE = {'Premature (screen failure)'}>} distinct SUBJID)

the other way around I've read would be with -= but this doesn't work and always return 0

Count({<TERMINATION_TYPE -= {'Premature (screen failure)'}>} distinct SUBJID)

even more surprisingly to me counting the non-terminated works as expected:

Count({<ENSTATUS -= {'terminated'}>} distinct SUBJID)

So, why are the excluded TERMINATION_TYPE records are not counted?

Thanks,

Marc

[m_woolf]

There's probably some fancy syntax you could use to make this work, but I recommend changing your load script so the TERMINATION_TYPE all have values. Something like:

load
       If(isnull(TERMINATION_TYPE),''No Failure',TERMINATION_TYPE) as TERMINATION_TYPE,

Then use set analysis to count TERMINATION_TYPE={'No Failure'}

[Vegar · Accepted solution]

It is your NULLs that is causing you trouble with SET analysis. 

Try one of these two.

=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)

=Count({$-$<TERMINATION_TYPE ={'Premature (screen failure)'}>} distinct SUBJID)

[mlattemann]

=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)

This seems to work. Thanks a lot. "E" stands for Exclude? As I said before, I am really struggling with the syntax of the set analysis...

Thanks,

Marc

[mlattemann]

@m_woolf:

well, this would be wrong... The termination type is not "No Failure" they are simply not terminated. The source data contains a lot of conditional answers where a lot of values missing, since these depend on other values. (e.g. in the database the question regarding termination type is disabled until the question Termination is not ticked). If I will need to fill all these missing values with some "dummy" values is a lot of work which then also need to be excluded in other counts.

Nevertheless, your proposed solution is working, though.

Thanks,

Marc

[tresesco]

Count({<TERMINATION_TYPE -= {'Premature (screen failure)'}>} distinct SUBJID)

The above doesn't work, because most probably your field's other values are NULLs. And nulls in set analysis is not considered to be part of ALL values or '*'. Here minus operator looks for other values except nulls. Therefore to handle NULL cases like yours, try like:

Count({$ - <TERMINATION_TYPE = {'Premature (screen failure)'}>} distinct SUBJID)

Or, you might want to replace '$' with '1' if you want the result ignorant to selections.   

[Vegar]

Yes. You are selecting the SUBJID that are not associated with the 'Premature...' termination type.