Select only clients that have all their accounts closed

[nicnad]

Hi,

I have a table like the following :

Name	Account_Number	END_DATE
Bob	12312	31DEC9999
Bob	653454	31DEC9999
Bob	52423	01NOV2006
Martin	56637	31DEC9999
Martin	2656	31DEC9999
Lewis	7454	01JUL2003
Lewis	78852	01JUN2007
Nicolas	696954	05MAY2011

The date 31DEC9999 means that the account is still open

How do I write a SAS or SQL procedure so that I can only select the clients name that have all their account closed ( END_DATE before today)?

The result would be :

Lewis

Nicolas

Thank you for your help and time.

[Tom · Accepted solution]

data have ;

length name $20 account_number 8 end_date 8;

informat end_date date9.;

format end_date date9.;

input Name Account_Number END_DATE;

cards;

Bob 12312 31DEC9999

Bob 653454 31DEC9999

Bob 52423 01NOV2006

Martin 56637 31DEC9999

Martin 2656 31DEC9999

Lewis 7454 01JUL2003

Lewis 78852 01JUN2007

Nicolas 696954 05MAY2011

run;

proc sql ;

select name from have

group by name

having max(end_date) < '31DEC9999'd

;

quit;

[Vince28_Statcan]

My bad, I deleted my previous post I totally missed out that you could have multiple records for a single account, some of which dated back when the account was still open.

Tom's by processing will do it. If END_DATE is stored as character instead of numeric in your DS, simply replace max(end_date) by max(input(end_date, date9.))

Vincent

[nicnad]

Thank you both for your replies.

Tom solution works great!

[nicnad]

Quick question.

What if I have account where the end date is  empty. An empty end date should return the same as if the end date was 31DEC9999.

The table would be like this and should return Nicolas and Lewis :

data have ;

length name $20 account_number 8 end_date 8;

informat end_date date9.;

format end_date date9.;

input Name Account_Number END_DATE;

cards;

Bob 12312 .

Bob 653454 .

Bob 52423 01NOV2006

Martin 56637 .

Martin 2656 .

Lewis 7454 01JUL2003

Lewis 78852 01JUN2007

Nicolas 696954 05MAY2011

run;

Thank you very much for your help and time!

[Haikuo]

Just add another condition to Tom's code:

having max(end_date) < '31DEC9999'd And min(end_date) >.

Haikuo

[nicnad]

min(end_date) isn't recognized as being . so in your example bob is selected because his min end_date is recognized as being 01nov2006.

Bob should not be selected because even though he has an account closed on 01nov2006 he has other accounts still open ( other accounts equal to .)

Hope my explication is clear.

Thank you for your help

[nicnad]

Does anyone has an idea or do I have to clarify my problem?

Thank you all for your help and time.

Really appreciated.

[DBailey]

proc sql ;

select name from have

group by name

having max(coalesce(end_date,'31Dec9999'd)) < '31DEC9999'd

;

quit;

[nicnad]

Simple yet brilliant.

Thank you very much.

[Ksharp]

proc sql ;

select name from have

group by name

having sum(end_date gt today())=0 and sum(missing(end_date))=0

;

quit;

Ksharp