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.
@SAStastic_Day 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.