Find All Full Years Between Two Dates

[Jeff_DOC]

Hello.

I was wondering if anyone could give me a hint on how to determine each full year between two dates? I later need to find an incident between those two dates. I don't have any prepared code since this is just a "I wonder if SAS can do this?"

ID	date_01	date_02		ID	want_01	want_02
158	7/15/1994	9/9/2004		158	**	**
268	6/10/2003	8/21/2007		158	1/1/1995	12/31/1995
				158	1/1/1996	12/31/1996
				158	1/1/1997	12/31/1997
				158	1/1/1998	12/31/1998
				158	1/1/1999	12/31/1999
				158	1/1/2000	12/31/2000
				158	1/1/2001	12/31/2001
				158	1/1/2002	12/31/2002
				158	1/1/2003	12/31/2003
				268	**	**
				268	1/1/2004	12/31/2004
				268	1/1/2005	12/31/2005
				268	1/1/2006	12/31/2006

[Kurt_Bremser]

You might have problems trying to make SAS prepare your coffee, but when it comes to date calculations, SAS has all tools needed, particularly the INTNX function:

data have;
input id $ date_01 :mmddyy10. date_02 :mmddyy10.;
format date: yymmdd10.;
datalines;
158 7/15/1994 9/9/2004
268 6/10/2003 8/21/2007
;

data want;
set have;
format want_01 want_02 yymmdd10.;
output;
want_01 = intnx("year",date_01,1,"b");
do while (want_01 lt intnx("year",date_02,0,"b"));
  want_02 = intnx("year",want_01,0,"e");
  output;
  want_01 = intnx("year",want_01,1,"b");
end;
drop date_01 date_02;
run;

[sbxkoenk]

Something like this?

data have;
input ID (date_01 date_02) (:mmddyy10.);
format date_01 date_02 date9.;
datalines;
158	7/15/1994 9/9/2004
268	6/10/2003 8/21/2007
;
run;

proc sort data=have; by ID; run;

data want;
 set have;
 by ID;
 do counter = 0 to intck('year',date_01,date_02);
    year_start = intnx('year',date_01,counter);
	year_stop  = intnx('year',date_01,counter+1) - 1;
    output;
 end;
 format year_start year_stop date9.;
run;

data want;
 set want;
 by ID;
 if (first.ID and date_01 > year_start) then delete;
 if (last.ID  and date_02 < year_stop ) then delete;
run;
/* end of program */

BR, Koen

[sbxkoenk]

I see now that @Kurt_Bremser has used the (optional) "alignment" as fourth argument in the INTNX function.

'beginning' (or 'b') and 'end' (or 'e')

That's of course better than my construction (just above) to find 'end'.

BR, Koen

[Ksharp]

data have;
input id $ date_01 :mmddyy10. date_02 :mmddyy10.;
format date: yymmdd10.;
datalines;
158 7/15/1994 9/9/2004
268 6/10/2003 8/21/2007
;

data want;
set have;
do date=date_01 to intnx('year',date_02,0)-1;
 if day(date)=1 and month(date)=1 then do;
   want_01=date;
   want_02=intnx('year',date,0,'e');
   output;
 end;
end;
format want_01 want_02 yymmdd10.;
drop date;
run;

[Quentin]

How do you want to handle the edge case of date_01=01/01/2023 date_02=12/31/2023?  Do you want that to be considered a full year?  

[sbxkoenk]

Also ... you said "I later need to find an incident between those two dates.".

Perhaps you’re taking an unnecessary extra step (an unnecessary detour)?

For example, if I want to know how often a variable takes on a value between 900 and 1000 … and this on an annual basis … I can simply use a format.

Like here:

proc means data=sashelp.citiday N;
 where SNYDJCM between 900 and 1000;
 class  date;
 var    SNYDJCM;
 format date year.;
run; 
/* end of program */

[EDIT]: Please note: the programme above does not take into account incomplete years

BR, Koen