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

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      .                          .

;

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

2 REPLIES 2
Haikuo
Onyx | Level 15

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.

dsbihill
Obsidian | Level 7

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.

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
  • 2 replies
  • 1120 views
  • 0 likes
  • 2 in conversation