Hi All,
I am struggling to create new FROM and VALID to dates from yearly snapshots. I need to create the dates based on change in HEIGHT. I have posted a IN and OUT data. Please see if you could help. Need to create the out data.
data in;
informat id 1. valid_from MMDDYY10. valid_to MMDDYY10. height 8.;
input id valid_from valid_to height;
format valid_from date9. valid_to date9.;
cards;
1 01/01/2010 01/01/2011 3
1 01/01/2011 01/01/2012 4
1 01/01/2012 01/01/2013 4
1 01/01/2013 01/01/2014 4
1 01/01/2014 01/01/2015 13
1 01/01/2015 01/01/2016 3
2 01/01/2011 01/01/2012 20
2 01/01/2012 01/01/2013 20
2 01/01/2013 01/01/2014 21
;
run;
data out;
informat id 1. valid_from MMDDYY10. valid_to MMDDYY10. height 8.;
input id valid_from valid_to height;
format valid_from date9. valid_to date9.;
cards;
1 01/01/2010 01/01/2011 3
1 01/01/2011 01/01/2014 4
1 01/01/2014 01/01/2015 13
1 01/01/2015 01/01/2016 3
2 01/01/2011 01/01/2013 20
2 01/01/2013 01/01/2014 21
;
run;
data in; informat id 1. valid_from MMDDYY10. valid_to MMDDYY10. height 8.; input id valid_from valid_to height; format valid_from date9. valid_to date9.; cards; 1 01/01/2010 01/01/2011 3 1 01/01/2011 01/01/2012 4 1 01/01/2012 01/01/2013 4 1 01/01/2013 01/01/2014 4 1 01/01/2014 01/01/2015 13 1 01/01/2015 01/01/2016 3 2 01/01/2011 01/01/2012 20 2 01/01/2012 01/01/2013 20 2 01/01/2013 01/01/2014 21 ; run; data want; set in; by id height notsorted; retain temp; if first.height then temp=valid_from; if last.height then do;valid_from=temp;output;end; drop temp; run;
proc sort data=in;
by id ;
run;
data out;
set in;
by id height notsorted;
if last.height;
run;
Regards,
Naveen Srinivasan
Edited: I missed to read your question properly. Well, in any case when Xia keshan answers, one can blindly copy the genius. My apologies!
Thanks for replying. Yes it didn't take into account changing the from data in row 2 to 2011 - except that it works fine.
I had totally forgotton about the notsorted option...that was the missing like for me. Thanks
data in; informat id 1. valid_from MMDDYY10. valid_to MMDDYY10. height 8.; input id valid_from valid_to height; format valid_from date9. valid_to date9.; cards; 1 01/01/2010 01/01/2011 3 1 01/01/2011 01/01/2012 4 1 01/01/2012 01/01/2013 4 1 01/01/2013 01/01/2014 4 1 01/01/2014 01/01/2015 13 1 01/01/2015 01/01/2016 3 2 01/01/2011 01/01/2012 20 2 01/01/2012 01/01/2013 20 2 01/01/2013 01/01/2014 21 ; run; data want; set in; by id height notsorted; retain temp; if first.height then temp=valid_from; if last.height then do;valid_from=temp;output;end; drop temp; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.