Calculate Number Days and Display Current Row

[rich_f]

I have searched this site and Google to my wits end.  I have found everything that tells me how to calculate the difference, in days, between two dates.  I am looking to display that result a little differently.  

Here is what my results look like:

Here is the need:

The case was submitted to Agency ID 52 on 17 Dec 2019.  Agency ID 52 sent it back to the originator on the same day a few hours later.  I need to be able to show that Agency ID 1457 took 27 days to send it back.  Currently, my query shows that it sat for 27 days with ID 52 (Days Reviewed 1st screenshot).  Everything I have found to calculate the difference works like it should, with the exception of getting the result to display where I need it.  Essentially the "Need" column has just been shifted up one row compared to the original display.

I need the result in this format, for a visualization project I'm working.  Any advice or hints would be greatly appreciated.

[novinosrin · Accepted solution]

Hi Posting the data as plain text would help us to copy paste conveniently and work on the requirement, Anyways, have you tried a programming construct called "LOOK AHEAD"?

data want;
 merge have have(firstobs=2 keep=agency_id rename=agency_id=need);
 if review_action="SUBMIT" then call missing(need);
run;

[rich_f]

Hi, I will do that next time I post .  Your example below worked perfectly.  Thank you!!

[ballardw]

You might show the code you are using.