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

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:

rich_f_2-1586809751532.png

 

Here is the need:

rich_f_1-1586809471822.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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
Calcite | Level 5

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

ballardw
Super User

You might show the code you are using.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 401 views
  • 0 likes
  • 3 in conversation