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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 825 views
  • 0 likes
  • 3 in conversation