Help using Base SAS procedures

Select only clients that have all their accounts closed

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Select only clients that have all their accounts closed

Hi,

I have a table like the following :

NameAccount_NumberEND_DATE
Bob1231231DEC9999
Bob65345431DEC9999
Bob5242301NOV2006
Martin5663731DEC9999
Martin265631DEC9999
Lewis

7454

01JUL2003
Lewis7885201JUN2007
Nicolas69695405MAY2011

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.


Accepted Solutions
Solution
‎07-24-2013 02:03 PM
Super User
Super User
Posts: 7,035

Re: Select only clients that have all their accounts closed

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;

View solution in original post


All Replies
Solution
‎07-24-2013 02:03 PM
Super User
Super User
Posts: 7,035

Re: Select only clients that have all their accounts closed

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;

Super Contributor
Posts: 339

Re: Select only clients that have all their accounts closed

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

Regular Contributor
Posts: 186

Re: Select only clients that have all their accounts closed

Thank you both for your replies.

Tom solution works great!

Regular Contributor
Posts: 186

Re: Select only clients that have all their accounts closed

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!

Respected Advisor
Posts: 3,156

Re: Select only clients that have all their accounts closed

Just add another condition to Tom's code:

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


Haikuo

Regular Contributor
Posts: 186

Re: Select only clients that have all their accounts closed

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

Regular Contributor
Posts: 186

Re: Select only clients that have all their accounts closed

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

Thank you all for your help and time.

Really appreciated.

Super Contributor
Posts: 578

Re: Select only clients that have all their accounts closed

proc sql ;

select name from have

group by name

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

;

quit;

Regular Contributor
Posts: 186

Re: Select only clients that have all their accounts closed

Simple yet brilliant.

Thank you very much.

Super User
Posts: 10,018

Re: Select only clients that have all their accounts closed

proc sql ;

select name from have

group by name

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

;

quit;

Ksharp

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 570 views
  • 6 likes
  • 6 in conversation