DATA Step, Macro, Functions and more

Look Forward data step

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Look Forward data step

Hello all.  I am struggling to find a way to do the following.  I have a data set and any time i have a reading of >6 i need to pull into the same observation the next 2 dates and levels for the same task and road number.  Below is an example data set and the result I am looking for.  I found the attached website that gives examples of looking ahead one record but i have not been able to get any of it to work to look ahead 2.

Four methods of performing a look-ahead read - sasCommunity

Any help would be greatly appreciated.

Data set Have

data temp;

input road_number task_number $ read_date date9. read_level ;

format read_date date9.;

datalines;

9 2590A5 03MAY2014 11

9 2590A5 26APR2013 14

9 2590A5 27OCT2012 2

9 2590A5 28OCT2011 0

9 2590A5 27APR2011 0

9 2590A5 27JAN2011 0

9 2590A6 27JAN2011 0

9 2590A6 30JUL2010 7

9 2590A6 25JAN2010 4

9 2590A6 31JUL2009 0

9 2590A6 10JUN2006 1

9 2590A6 18MAR2006 0

9 2590A6 12DEC2005 .

9 2590A6 11SEP2005 2

9 2590A6 08MAR2005 0

9 2590A6 05DEC2004 9

9 2590A6 03SEP2004 0

10 2590A1 07JUL2014 0

10 2590A1 31DEC2013 0

10 2590A1 01JUL2013 0

10 2590A1 29DEC2012 1

;

Dataset need

Road_Number   Task_number          Read_date          Read_level     read_2_date     read_2_level          read_3_date     Read_3_level

9                          2590A5           03MAY2014                11           26APR2013          14                    27OCT2012             2

9                          2590A5           26APR2013                14           27OCT2012           2                     28OCT2011             0

9                          2590A6           30JUL2010                   7           25JAN2010           4                      31JUL2009             0

9                          2590A6           05DEC2004                  9           03SEP2004          0      .                          .

;


Accepted Solutions
Solution
‎01-07-2015 02:57 PM
Respected Advisor
Posts: 3,124

Re: Look Forward data step

I agree SAS should make the Lead a bit easier Smiley Happy

data want;

merge temp

        temp (firstobs=2 rename=(road_number=_road1 task_number=_task1 read_date=read_2_date read_level=read_2_level))

        temp (firstobs=3 rename=(road_number=_road2 task_number=_task2 read_date=read_3_date read_level=read_3_level))

;

if read_level >6;

if (road_number ne _road1 or task_number ne _task1) then call missing (read_2_date, read_2_level); 

if (road_number ne _road2 or task_number ne _task2) then call missing (read_3_date, read_3_level); 

drop _:;

run;

You could also use LAG() function, but it is about the same verbose level.

View solution in original post


All Replies
Solution
‎01-07-2015 02:57 PM
Respected Advisor
Posts: 3,124

Re: Look Forward data step

I agree SAS should make the Lead a bit easier Smiley Happy

data want;

merge temp

        temp (firstobs=2 rename=(road_number=_road1 task_number=_task1 read_date=read_2_date read_level=read_2_level))

        temp (firstobs=3 rename=(road_number=_road2 task_number=_task2 read_date=read_3_date read_level=read_3_level))

;

if read_level >6;

if (road_number ne _road1 or task_number ne _task1) then call missing (read_2_date, read_2_level); 

if (road_number ne _road2 or task_number ne _task2) then call missing (read_3_date, read_3_level); 

drop _:;

run;

You could also use LAG() function, but it is about the same verbose level.

Contributor
Posts: 53

Re: Look Forward data step

Thank you so very  much,  After coding it in with a couple more variables that i wanted to bring over works perfectly.  I was so far out in left field I would have been next year before i got there.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 314 views
  • 0 likes
  • 2 in conversation