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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;


View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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!

Jane7476
Calcite | Level 5

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

Ksharp
Super User

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;


hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 3 replies
  • 1430 views
  • 0 likes
  • 3 in conversation