topic Use Retain to Determine Number of Days Between Dates in SAS Programming

Use Retain to Determine Number of Days Between Dates

Jeff_DOC — Tue, 11 Nov 2025 00:26:12 GMT

Good afternoon.

I need a data step to determine the number of days between succeeding rows based on a group (see the example). I've tried to use RETAIN to achieve this, but I haven't been able to make any progress at all.

Could someone point me in the correct direction to perform this? I don't have an example since I haven't gotten anything to work.

Thank you very much to anyone willing to help.

KEY	REVIEW_DATE	REVIEW_TYPE	Days	Comment
380	3/31/2023	INIT	0	All INIT with the same KEY begin with zero
380	9/6/2023	6MONTH	159	Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
380	3/6/2024	6MONTH	182	Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
380	9/18/2024	6MONTH	196	Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
380	4/4/2025	6MONTH	198	Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
381	2/22/2024	INIT	0	All INIT with the same KEY begin with zero
381	6/30/2024	6MONTH	129	Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
381	7/1/2025	6MONTH	366	Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.

Re: Use Retain to Determine Number of Days Between Dates

SASKiwi — Tue, 11 Nov 2025 01:19:09 GMT

Here's one way of doing this - using RETAIN requires use of a variable not being read in:

data Reviews;
  input @1 key $3. @6 Review_Date mmddyy10.;
  format Review_Date date9.;
  datalines;
380   3/31/2023
380	  9/6/2023
380	  3/6/2024
380	  9/18/2024
380	  4/4/2025
381	  2/22/2024
381	  6/30/2024
381	  7/1/2025
;
run;

data Reviews_Want;
  keep key Review_Date Days;
  set Reviews;
  by key;
  if first.key then Days = 0;
  else Days = Review_Date - Last_Review_Date;
  output;
  retain Last_Review_Date;
  Last_Review_Date = Review_Date;
run;

Re: Use Retain to Determine Number of Days Between Dates

Tom — Tue, 11 Nov 2025 01:18:02 GMT

First let's convert your listing into an actual SAS dataset so we have something to program with.

data have;
  infile cards dsd dlm='|' truncover ;
  input KEY REVIEW_DATE :mmddyy. REVIEW_TYPE $ Days Comment :$100. ;
  format review_date yymmdd10.;
cards;
380|3/31/2023|INIT|0|All INIT with the same KEY begin with zero
380|9/6/2023|6MONTH|159|Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
380|3/6/2024|6MONTH|182|Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
380|9/18/2024|6MONTH|196|Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
380|4/4/2025|6MONTH|198|Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
381|2/22/2024|INIT|0|All INIT with the same KEY begin with zero
381|6/30/2024|6MONTH|129|Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
381|7/1/2025|6MONTH|366|Subsequent rows with the same key determine the number of days from the preceding row REVIEW_DATE.
;

So it looks like from they values you show of DAYS that you don't actually want to RETAIN anything. Instead you just want to use the PREVIOUS value, which you can so with the LAG() function. Make not to execute LAG() conditionally, it can only return values that you previously passed it. Also make sure to remember to ignore/replace the values calculated for the FIRST observation in a group since the previous date value is from the previous group.

data want;
  set have;
  by key review_date;
  new_days = review_date - lag(review_date);
  if first.key then new_days=0;
run;

Results

You would want to use the RETAIN statement if your goal was instead to calculate the difference since the INIT record's date (usually called a BASELINE date).

data want ;
  set have;
  by key review_date ;
  retain baseline ;
  format baseline yymmdd10.;
  if first.key then call missing(baseline);
  if review_type='INIT' then baseline=review_date ;
  new_days = review_date - baseline;
run;

Re: Use Retain to Determine Number of Days Between Dates

ballardw — Tue, 11 Nov 2025 06:12:29 GMT

Don't forget the DIF function:

new_days = review_date - lag(review_date);

is the same as

new_days = dif(review_date);

Same concern as LAG for conditional operation.

Re: Use Retain to Determine Number of Days Between Dates

Jeff_DOC — Wed, 19 Nov 2025 17:31:23 GMT

Sorry about the lateness of my solution acceptance. This works perfectly and gives me what I need to begin building in my conditional logic. It also explained the lag function to me as it's not something I've used before, so thank you for all of that.