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 . .
;
I agree SAS should make the Lead a bit easier
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.
I agree SAS should make the Lead a bit easier
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.
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.
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.
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.