BookmarkSubscribeRSS Feed
purpleclothlady
Pyrite | Level 9

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

purpleclothlady_0-1680198065365.png

 

 

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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;
   
--
Paige Miller
purpleclothlady
Pyrite | Level 9

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_0-1680204357225.png

 

 

 

purpleclothlady
Pyrite | Level 9

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;
 
 

 

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 175 views
  • 3 likes
  • 2 in conversation