topic Re: increment an age for every year between 2 dates and create a new row for each year with the new in SAS Programming

increment an age for every year between 2 dates and create a new row for each year with the new age

ItWorked — Wed, 22 May 2024 16:31:50 GMT

Hello,

I am trying to figure out how to expand a dataset by creating a new row for each year between a start date and end date and find the age the person is at each year between those 2 dates. I have tried a couple of things without success.

I have tried to modify this code: https://stackoverflow.com/questions/47428867/how-to-create-additional-rows-for-each-day-between-two-dates-in-sas.Though the code ran successfully without errors, I did not get the intended results. It incremented the first observation's start date, and I need the first observation's start date to remain unchanged. I am not certain how to proceed. Any assistance would be appreciated. I am using SAS9.4.

Re: increment an age for every year between 2 dates and create a new row for each year with the new

A_Kh — Wed, 22 May 2024 17:08:09 GMT

We can use OUTPUT statement to read the initial records, then DO LOOPS with shifting function INTNX to increment AGE and YEAR by 1 adding an extra observation for each year:

data want; 
	set have;
	output; 
	do i=Year(startdate) to year(enddate)-1 by 1;
		startdate=intnx('year', startdate, 1, 'same');
		age+1;
		output;
	end;
	drop i; 
run;

Re: increment an age for every year between 2 dates and create a new row for each year with the new

ballardw — Wed, 22 May 2024 17:20:21 GMT

Pretty trivial if you add a new variable.

Note: if you can't provide example data in the form of a working data step, see below, at least provide it as text pasted into a text box opened on the forum with the </> icon. Most of us are not going to retype any significant amount of stuff from a picture.

data work.have;
   input id dob :mmddyy10. startdate :mmddyy10. enddate :mmddyy10.;
   format dob startdate enddate mmddyy10.;
datalines;
100  4/4/1968 2/12/2003 5/9/2007
200 1/29/1944 8/19/2009 9/16/2011
300 5/16/1977 3/3/2001 5/9/2007
;

data work.want;
   set work.have;
   intervalstart=startdate;
   do until (intervalstart > enddate);
         output;
         intervalstart=intnx('year',intervalstart,1,'S');
   end;
   format intervalstart mmddyy10.;
run;

An exercise for the interested reader where to increment the "age".

Personally i want to know which "date" an age is calculated for.

@ItWorked wrote:

Hello,

I am trying to figure out how to expand a dataset by creating a new row for each year between a start date and end date and find the age the person is at each year between those 2 dates. I have tried a couple of things without success.

I have tried to modify this code: https://stackoverflow.com/questions/47428867/how-to-create-additional-rows-for-each-day-between-two-dates-in-sas.Though the code ran successfully without errors, I did not get the intended results. It incremented the first observation's start date, and I need the first observation's start date to remain unchanged. I am not certain how to proceed. Any assistance would be appreciated. I am using SAS9.4.

Re: increment an age for every year between 2 dates and create a new row for each year with the new

ItWorked — Wed, 22 May 2024 18:38:46 GMT

This was perfect; I appreciate you!

Re: increment an age for every year between 2 dates and create a new row for each year with the new

ItWorked — Wed, 22 May 2024 18:42:03 GMT

@ballardw Thank you for the tip. I did not know this. Thank you for re-writing the code as well.