DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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!

 

AccountBreedBillLtr1_dteLtr2_dteltr3_dteLtr4_dteLtr5_dteRegionBalance
456Tabby0016/15/200910/13/2009   990.00
456Tabby0016/15/200910/13/20094/15/2010  99100.00
456Tabby0016/15/200910/13/20094/15/2010 7/7/201099100.00
456Tabby0016/15/200910/13/20094/15/2010 7/7/201099100.00
456Tabby001 10/13/20094/15/2010 7/7/201099100.00
456Tabby001 10/13/20094/15/2010 7/7/201099100.00
456Tabby001 10/13/20094/15/2010 7/7/201099100.00
456Tabby001      100.00
456Tabby001      100.00
456Tabby001      100.00
456         
456         

Accepted Solutions
Solution
‎05-15-2018 04:25 PM
Super User
Posts: 6,921

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

Posted in reply to ScotchCat

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.

View solution in original post


All Replies
Respected Advisor
Posts: 3,258

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

Posted in reply to ScotchCat

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

--
Paige Miller
New Contributor
Posts: 4

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

Posted in reply to PaigeMiller
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.
Super User
Posts: 13,913

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

Posted in reply to ScotchCat

@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?

Respected Advisor
Posts: 3,258

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

Posted in reply to ScotchCat

@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.

--
Paige Miller
New Contributor
Posts: 4

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

Posted in reply to PaigeMiller

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,   

Respected Advisor
Posts: 4,779

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

[ Edited ]
Posted in reply to ScotchCat

@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? 

 

Super User
Posts: 2,049

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

[ Edited ]
Posted in reply to ScotchCat
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;
Solution
‎05-15-2018 04:25 PM
Super User
Posts: 6,921

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

Posted in reply to ScotchCat

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.

New Contributor
Posts: 4

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

Posted in reply to Astounding

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

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 207 views
  • 0 likes
  • 6 in conversation