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 |
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.
Please explain further. The hot pink row doesn't have all dates present.
@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?
@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.
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,
@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.
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?
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;
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.
Thank You So Much!! This is working!! I really appreciate it!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.