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.
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;
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;
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.
@ballardw Thank you for the tip. I did not know this. Thank you for re-writing the code as well.
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.
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.
Ready to level-up your skills? Choose your own adventure.