topic Re: Trying to select the most recent row of data that has all dates present in a data table in SAS Programming

Trying to select the most recent row of data that has all dates present in a data table

ScotchCat — Tue, 15 May 2018 19:25:54 GMT

I'm trying to select the most recent row of data that has all dates present within a data table. I've tried several things without success and need to resolve this. I'm pretty new to SAS and only know basic commands. Here is an example of my data table, the row of data I need is the one highlighted in 'Hot Pink' Any help is appreciated!

Account	Breed	Bill	Ltr1_dte	Ltr2_dte	ltr3_dte	Ltr4_dte	Ltr5_dte	Region	Balance
456	Tabby	001	6/15/2009	10/13/2009				99	0.00
456	Tabby	001	6/15/2009	10/13/2009	4/15/2010			99	100.00
456	Tabby	001	6/15/2009	10/13/2009	4/15/2010		7/7/2010	99	100.00
456	Tabby	001	6/15/2009	10/13/2009	4/15/2010		7/7/2010	99	100.00
456	Tabby	001		10/13/2009	4/15/2010		7/7/2010	99	100.00
456	Tabby	001		10/13/2009	4/15/2010		7/7/2010	99	100.00
456	Tabby	001		10/13/2009	4/15/2010		7/7/2010	99	100.00
456	Tabby	001							100.00
456	Tabby	001							100.00
456	Tabby	001							100.00
456
456

Re: Trying to select the most recent row of data that has all dates present in a data table

PaigeMiller — Tue, 15 May 2018 19:32:26 GMT

Please explain further. The hot pink row doesn't have all dates present.

Re: Trying to select the most recent row of data that has all dates present in a data table

ScotchCat — Tue, 15 May 2018 19:36:33 GMT

The example is how the data table is structured, the letter dates can have 'missing' values or 'holes'. That has added to this challenge, it's not a simple Max date situation.

Re: Trying to select the most recent row of data that has all dates present in a data table

ballardw — Tue, 15 May 2018 19:41:19 GMT

@ScotchCat wrote:
The example is how the data table is structured, the letter dates can have 'missing' values or 'holes'. That has added to this challenge, it's not a simple Max date situation.

Then you will need to provide a much clearer definition of what exactly entails "all dates present" and "most recent".

I suspect the issue is having any record with more than one "letter date" at all. Did you reformat another data table to get this structure or did someone using a spreadsheet expect you to magically read their mind for intent?

Re: Trying to select the most recent row of data that has all dates present in a data table

PaigeMiller — Tue, 15 May 2018 19:42:42 GMT

@ScotchCat wrote:
The example is how the data table is structured, the letter dates can have 'missing' values or 'holes'. That has added to this challenge, it's not a simple Max date situation.

Sorry, this is not an explanation.

Your original criterion "I'm trying to select the most recent row of data that has all dates present within a data table" is not met by the hot pink row, and your statement quoted above does not clarify what you are looking for.

Re: Trying to select the most recent row of data that has all dates present in a data table

Patrick — Tue, 15 May 2018 19:43:59 GMT

@ScotchCat wrote:
The example is how the data table is structured, the letter dates can have 'missing' values or 'holes'. That has added to this challenge, it's not a simple Max date situation.

@ScotchCat

Before going into coding one must determine the logic to be implemented. For your sample data:

1. What is the definition of all dates given that the row you've marked has missing dates?

2. What constitutes most recent row given that the non missing dates are all the same in a column?

Re: Trying to select the most recent row of data that has all dates present in a data table

novinosrin — Tue, 15 May 2018 19:45:54 GMT

data want;
call missing(flag);
do _n_=1 by 1 until(last.account);
set have;
by account;
if n(of Ltr:)=0 and missing(flag) then   flag=_n_-1;
end;
do _n_=1 by 1 until(last.account);
set have;
by account;
if flag=_n_ then output;
end;
run;

Re: Trying to select the most recent row of data that has all dates present in a data table

ScotchCat — Tue, 15 May 2018 20:11:06 GMT

My apologies if I'm not clear, I'll try to explain. In the table example, letters have been sent out, however not all were sent, leaving some of the columns blank. I'm looking for the last row of data that contains the most letter dates before the letter dates are all blank. Thanks,

Re: Trying to select the most recent row of data that has all dates present in a data table

Astounding — Tue, 15 May 2018 20:15:37 GMT

Given your subsequent description, it sounds like you need only one date present (or more, of course). Here's a simple way:

data want;

set have;

by account;

where (ltr1_date > . or ltr2_date > . or ltr3_date > . or ltr4_date > . or ltr5_date > .);

if last.account;

run;

If there are other conditions required (besides the presence of at least one date), we might be able to modify the WHERE statement ... depends on what the conditions are.

Re: Trying to select the most recent row of data that has all dates present in a data table

ScotchCat — Tue, 15 May 2018 20:26:24 GMT

Thank You So Much!! This is working!! I really appreciate it!!