BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nicnad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Fluorite | Level 6

Thank you both for your replies.

Tom solution works great!

nicnad
Fluorite | Level 6

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
Onyx | Level 15

Just add another condition to Tom's code:

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


Haikuo

nicnad
Fluorite | Level 6

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
Fluorite | Level 6

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

Thank you all for your help and time.

Really appreciated.

DBailey
Lapis Lazuli | Level 10

proc sql ;

select name from have

group by name

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

;

quit;

nicnad
Fluorite | Level 6

Simple yet brilliant.

Thank you very much.

Ksharp
Super User

proc sql ;

select name from have

group by name

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

;

quit;

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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