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

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         
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ScotchCat
Obsidian | Level 7
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.
ballardw
Super User

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

PaigeMiller
Diamond | Level 26

@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
ScotchCat
Obsidian | Level 7

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,   

Patrick
Opal | Level 21

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

 

novinosrin
Tourmaline | Level 20
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;
Astounding
PROC Star

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.

ScotchCat
Obsidian | Level 7

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

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!

How to Concatenate Values

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.

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
  • 9 replies
  • 1086 views
  • 0 likes
  • 6 in conversation