Backfilling with following row details

Question

I have a table like the 1st 3 columns, and need to add the fourth column:

Sequence ID Last Status  Current Status
1        1               New
2        1  New          Open    
3        1               Open
4        1               Open
5        1               Open
6        1  Open         Closed
7        1               Closed
8        1               Closed
9        1  Closed       
10       2               New
11       2  New          Open
12       2               Open
13       2  Open         Closed
14       2  Closed       
15       3               New
16       3  New          Open
etc.

Basically it currently only shows the last status at the point the status was changed - I need to display the 'current' status at the time.

I'm new to SQL, and thought I could use LEAD, but unfortunately it's not supported in SAS.

Thanks!

score 2 · Answer 1 · answered Mar 25 '13 at 14:29

2

The following SQL will work in most dialects, including SAS SQL:

select t.quence, t.id, t.lastStatus, tnext.lastStatus as CurrentStatus
from (select t.*,
             (select min(Sequence) from t t2 where t2.id = t.id and t2.sequence > t.sequence and t2.LastStatus is not null
             ) as NextId
      from t
     ) t left out join
     t tnext
     on t.sequence = tnext.sequence

It finds the next status record with the same id (if any) and then joins the results back in to get the status.

In SAS, I would be more inclined to do this use data step code. If the data is stored in a particular database, there may be simpler solutions specific to the database.

answered Mar 25 '13 at 14:29

Gordon Linoff

1,242,037
58
646
786

Unfortunately I couldn't get this to work. I'm sure it's just me though, so I'll have aother try another day. thanks for your help. – Dazed and Confused Mar 26 '13 at 12:45
I think this works: `code` select t.Sequence, t.id, t.lastStatus, tnext.lastStatus as CurrentStatus from (select t.*, (select min(Sequence) from t t2 where t2.id = t.id and t2.sequence > t.sequence and t2.LastStatus is not null ) as NextId from t ) t left outer join t tnext on t.NextId= tnext.sequence `code` – HywelMJ Dec 07 '18 at 15:30

score 2 · Accepted Answer · answered Mar 25 '13 at 16:56

Here's the data step code that should do what you want. It doesn't do the final row correctly, since you don't have any further rows to indicate that it should be Open. If there is a data logic rule that can be applied, that should be easy to add.

It does require two sorts, which can be expensive, but likely the SQL Join will require similar maneuvers behind the scenes. If you have large amounts of data, I recommend testing both methods to see which is faster.

data have;
input Sequence ID LastStatus  $;
infile datalines missover;
datalines;
1        1               
2        1  New           
3        1               
4        1               
5        1               
6        1  Open         
7        1               
8        1               
9        1  Closed       
10       2               
11       2  New          
12       2               
13       2  Open         
14       2  Closed       
15       3               
16       3  New          
;;;;
run;

proc sort data=have;
by id  descending sequence;
run;

data want;
set have;
length CurrentStatus $8;
by id  descending sequence;      *so the last sequence for each ID comes first;
retain CurrentStatus;            *keep its value for each row;
if first.id then 
 call missing(CurrentStatus);    *reset for each ID;
output;                          *put out the current line;
if not missing(LastStatus) then  
   CurrentStatus = LastStatus;   *now update CurrentStatus if needed;
run;

proc sort data=want;             *resort in logical order;
by id sequence;
run;

Thanks. I've found data steps faster in the past for similar things, and I can't get the SQL to run anyway. — Dazed and Confused, Mar 26 '13 at 12:47
You might be able to eliminate the 2 sorts and do this in a single data step by working through the dataset backwards: http://stackoverflow.com/questions/915419/sas-can-i-make-sas-process-observations-backwards I'm not sure whether this will work with a by statement, but it's worth a shot. — user667489, Mar 26 '13 at 14:32
Unfortunately, no; you lose LAST and FIRST when using random access (POINT). It would be nice if DESCENDING worked in such a way as to allow you to do ASCENDING or DESCENDING from the same sort, but that would be difficult or impossible to implement. — Joe, Mar 27 '13 at 06:56

Backfilling with following row details

2 Answers2