topic Re: Calculate Number Days and Display Current Row in New SAS User

Calculate Number Days and Display Current Row

rich_f — Mon, 13 Apr 2020 20:37:42 GMT

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.

Re: Calculate Number Days and Display Current Row

novinosrin — Mon, 13 Apr 2020 20:59:13 GMT

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;

Re: Calculate Number Days and Display Current Row

ballardw — Mon, 13 Apr 2020 20:57:52 GMT

You might show the code you are using.

Re: Calculate Number Days and Display Current Row

rich_f — Tue, 14 Apr 2020 13:30:03 GMT

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