topic Re: Select only clients that have all their accounts closed in SAS Procedures

Select only clients that have all their accounts closed

nicnad — Wed, 24 Jul 2013 17:44:28 GMT

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.

Re: Select only clients that have all their accounts closed

Tom — Wed, 24 Jul 2013 18:03:29 GMT

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;

Re: Select only clients that have all their accounts closed

Vince28_Statcan — Wed, 24 Jul 2013 18:09:50 GMT

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

Re: Select only clients that have all their accounts closed

nicnad — Wed, 24 Jul 2013 18:11:50 GMT

Thank you both for your replies.

Tom solution works great!

Re: Select only clients that have all their accounts closed

nicnad — Wed, 24 Jul 2013 19:18:53 GMT

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!

Re: Select only clients that have all their accounts closed

Haikuo — Wed, 24 Jul 2013 19:33:01 GMT

Just add another condition to Tom's code:

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

Haikuo

Re: Select only clients that have all their accounts closed

nicnad — Wed, 24 Jul 2013 19:39:41 GMT

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

Re: Select only clients that have all their accounts closed

nicnad — Wed, 24 Jul 2013 20:38:27 GMT

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

Thank you all for your help and time.

Really appreciated.

Re: Select only clients that have all their accounts closed

DBailey — Wed, 24 Jul 2013 20:52:23 GMT

proc sql ;

select name from have

group by name

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

;

quit;

Re: Select only clients that have all their accounts closed

nicnad — Wed, 24 Jul 2013 21:07:55 GMT

Simple yet brilliant.

Thank you very much.

Re: Select only clients that have all their accounts closed

Ksharp — Thu, 25 Jul 2013 02:34:40 GMT

proc sql ;

select name from have

group by name

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

;

quit;

Ksharp