BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ItWorked
Calcite | Level 5

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-...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.  

data have.PNGdata want.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
A_Kh
Barite | Level 11

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; 

 

View solution in original post

4 REPLIES 4
A_Kh
Barite | Level 11

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; 

 

ballardw
Super User

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-...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.  

data have.PNGdata want.PNG


 

ItWorked
Calcite | Level 5

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 782 views
  • 0 likes
  • 3 in conversation