I have a database with about 900,000 rows that represent a person using a state service. One person can have many rows, a new one is generated each time they start a new service.

I need to select only those cases where the person is not using ANY services at this time and can't figure how to do it.

I need the program to look at all rows that have the same ID number (each person is always entered using the same ID number, no matter the service), then determine if all of a person's services (if multiple rows) are Inactive (there is a variable for
that - can be inactive or pending or active). If all rows for the same person are inactive, those are the cases I want to keep.

Obviously if I were to Select Cases for "IN", I will get a list of inactive services, but the person associated could have had other services that are still active that got cut.

Guidance please...and thank you!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thu, 30 Apr 2020 11:25:55 -0700 (PDT), dianasodavis@gmail.com
wrote:

I have a database with about 900,000 rows that represent a person using a state service. One person can have many rows, a new one is generated each time they start a new service.

I need to select only those cases where the person is not using ANY services at this time and can't figure how to do it.

I need the program to look at all rows that have the same ID number (each person is always entered using the same ID number, no matter the service), then determine if all of a person's services (if multiple rows) are Inactive (there is a variable for

that - can be inactive or pending or active). If all rows for the same person are inactive, those are the cases I want to keep.

Obviously if I were to Select Cases for "IN", I will get a list of inactive services, but the person associated could have had other services that are still active that got cut.

Guidance please...and thank you!

One way -
Make a new variable where InactiveYN = 1 for Yes, 0 for other.

Do an Aggregate to get the Mean(InactiveYN) which you
(use the option to) save as a new variable to each record.

Select the cases where that mean is 1.0.

--
Rich Ulrich

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Ah ha! That makes sense...I will try tomorrow. I recognize your name from reading through other questions to search for an answer...thank you very much for sharing your knowledge to help others.

I will let you know.
Diana

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)