Get missing dates from a series of dates

[purpleclothlady]

hi all:

 I am trying to find out the gap dates and output them.

Thank you,

Purple

data have;
input NAME $ 1-4 start $ 5-14 end $ 15-24 E 25-29  ;
datalines;
001 27DEC2022 28DEC2022 12.3
001 28DEC2022 28DEC2022 13.1
001 28DEC2022 29DEC2022 14.0
001 31DEC2022 31DEC2022 14.5
001 31DEC2022 04JAN2023 15.5
002 08MAR2023 10MAR2023 13.0
002 10MAR2023 11MAR2023 14.0
002 16MAR2023 18MAR2023 15.0
;
run;

Goal:
1) output all dates
eg 001
output a column alldates from 27DEC2022 to 4Jan2023

2) find out which dates are gap.
eg 001 create anther column gap (missing dates) 30Dec2022

[PaigeMiller]

From now on, I advise you to work with dates as numeric variables and NOT as character strings. In fact, I don't just advise it, I consider it to be mandatory, not optional. You will spend 10X as much time and pull your hair out trying to solve this problem if your dates are character strings.

The code below produces the information you want. You may have to modify its appearance to match your exact output.

data have;
    input NAME $ 1-4 start :date9. end :date9. E 25-29  ;
    datalines;
001 27DEC2022 28DEC2022 12.3
001 28DEC2022 28DEC2022 13.1
001 28DEC2022 29DEC2022 14.0
001 31DEC2022 31DEC2022 14.5
001 31DEC2022 04JAN2023 15.5
002 08MAR2023 10MAR2023 13.0
002 10MAR2023 11MAR2023 14.0
002 16MAR2023 18MAR2023 15.0
;
data have;
	set have;
	alldates=start;
run;
/* Find minimum and maximum date by NAME */
proc summary nway data=have;
    class name;
    var start end;
    output out=minmax min(start)=mindate max(end)=maxdate;
run;
/* Generate a list of all dates for each NAME */
data have1;
	set minmax;
	do alldates=mindate to maxdate;
		output;
	end;
	drop mindate maxdate;
run;
/* Create final output */
data want;
	merge have1 have;
	by name alldates;
	format alldates start end date9.;
	drop mindate maxdate;
run;
   

[purpleclothlady]

Hi @PaigeMiller :

Thanks for quick response and advise.

The output didn't show the column - Missing. I don't know how to get this column. 

[purpleclothlady]

I added one step and got the Missing column. thanks all for this amazing forum.

data want2;
set want;
by name;

/*lag function to carry forward END*/
 retain a ;
  if first.name then call missing(a);
	
  if end ne . then a=end;	 else end=a; format a end date9.;

/*compare END with alldates, any dates out of boundary are Missing dates*/
	if alldates>end then missing=alldates; format missing date9.;
	keep name start end e alldates missing ;
run;