Adapting/taking values from the next row

[farshidowrang]

Dear programmers,

I send many wellbores through the different layers of the earth like a snake. When a wellbore goes from a layer to another layer a I like to mark it in this way:

When a specific wellbore goes from a layer to another, the last row of the previous layer shall take the DEPTH value from the next layer.

Note that the last layers have been duplicated.

Note also that for every WELLBORE_NAME the DEPTH never repeats but the EARTH_LAYER often does.

My dataset is:

WELLBORE_NAME	DEPTH	ERATH_LAYER
A	12000	E
A	12100	E
A	12100	E
A	12200	K
A	12300	K
A	12400	K
A	12400	K
A	12500	Z
A	12600	Z
A	12900	Z
A	12900	Z
B	24000	E
B	24100	E
B	24200	E
B	24200	E
B	24500	L
B	24900	L
B	25000	L
B	25000	L
B	25100	K
B	25200	K
B	25400	K
B	25400	K
C	3500	E
C	3600	E
C	3600	E
C	3700	K
C	3800	K
C	3900	K
C	3900	K
C	4000	Z
C	4100	Z
C	4800	Z
C	4800	Z
C	4900	E
C	5000	E
C	5000	E
C	5100	Z
C	5200	Z
C	5200	Z
C	5201	K
C	5201	K
C	5202	Z
C	5203	Z
C	5203	Z
C	5204	K
C	5204	K
C	5205	Z

What I need is the following. Note the italic bold values inside the table. They have adapted the DEPTH value from the next layer/row.

WELLBORE_NAME	DEPTH	ERATH_LAYER
A	12000	E
A	12100	E
A	12200	E
A	12200	K
A	12300	K
A	12400	K
A	12500	K
A	12500	Z
A	12600	Z
A	12900	Z
A	24000	Z
B	24000	E
B	24100	E
B	24200	E
B	24500	E
B	24500	L
B	24900	L
B	25000	L
B	25100	L
B	25100	K
B	25200	K
B	25400	K
B	3500	K
C	3500	E
C	3600	E
C	3700	E
C	3700	K
C	3800	K
C	3900	K
C	4000	K
C	4000	Z
C	4100	Z
C	4800	Z
C	4900	Z
C	4900	E
C	5000	E
C	5100	E
C	5100	Z
C	5200	Z
C	5201	Z
C	5201	K
C	5202	K
C	5202	Z
C	5203	Z
C	5204	Z
C	5204	K
C	5205	K
C	5205	Z

I would be very grateful if you help me with this!

Best regards

Farshid Owrang  

[s_lassen · Accepted solution]

Here is a solution using SET with POINT=

data have;                                                                                                                              
input WELLBORE_NAME $ DEPTH EARTH_LAYER $;                                                                                              
cards;                                                                                                                                  
A 12000  E                                                                                                                              
A 12100  E                                                                                                                              
A 12100  E                                                                                                                              
A 12200  K                                                                                                                              
A 12300  K                                                                                                                              
A 12400  K                                                                                                                              
A 12400  K                                                                                                                              
A 12500  Z                                                                                                                              
A 12600  Z                                                                                                                              
A 12900  Z                                                                                                                              
A 12900  Z                                                                                                                              
B 24000  E                                                                                                                              
B 24100  E                                                                                                                              
B 24200  E                                                                                                                              
B 24200  E                                                                                                                              
B 24500  L                                                                                                                              
B 24900  L                                                                                                                              
B 25000  L                                                                                                                              
B 25000  L                                                                                                                              
B 25100  K                                                                                                                              
B 25200  K                                                                                                                              
B 25400  K                                                                                                                              
B 25400  K                                                                                                                              
C 3500  E                                                                                                                               
C 3600  E                                                                                                                               
C 3600  E                                                                                                                               
C 3700  K                                                                                                                               
C 3800  K                                                                                                                               
C 3900  K                                                                                                                               
C 3900  K                                                                                                                               
C 4000  Z                                                                                                                               
C 4100  Z                                                                                                                               
C 4800  Z                                                                                                                               
C 4800  Z                                                                                                                               
C 4900  E                                                                                                                               
C 5000  E                                                                                                                               
C 5000  E                                                                                                                               
C 5100  Z                                                                                                                               
C 5200  Z                                                                                                                               
C 5200  Z                                                                                                                               
C 5201  K                                                                                                                               
C 5201  K                                                                                                                               
C 5202  Z                                                                                                                               
C 5203  Z                                                                                                                               
C 5203  Z                                                                                                                               
C 5204  K                                                                                                                               
C 5204  K                                                                                                                               
C 5205  Z                                                                                                                               
;run;   
                                                                                                                                
data want;                                                                                                                              
  set have;                                                                                                                             
  by WELLBORE_NAME EARTH_LAYER notsorted;                                                                                               
  if last.EARTH_LAYER and not last.WELLBORE_NAME then do;                                                                               
    _N_=_N_+1;                                                                                                                          
    set have(keep=DEPTH) point=_N_;                                                                                                     
    end;                                                                                                                                
run;

[farshidowrang]

Seemingly it works!

Thank you very much my friend!

Best regards

Farshid

[ed_sas_member]

Hi @farshidowrang 

You can also try this:

data dupkey;
	set have;
	by WELLBORE_NAME ERATH_LAYER notsorted;
	if eof1=0 then set have (firstobs=2 keep=DEPTH rename=(DEPTH = LEAD_DEPTH)) end=eof1;
 	else LEAD_DEPTH=.;
 	if last.ERATH_LAYER then do;
 		DEPTH = LEAD_DEPTH;
 		output;
 	end;
 	keep WELLBORE_NAME DEPTH ERATH_LAYER;
run;

data want;
	set have dupkey;
run;

proc sort data=want;
	by WELLBORE_NAME DEPTH ERATH_LAYER;
run;

[Ksharp]

data want;                                                                                                                              
  merge have have(firstobs=2 keep=DEPTH EARTH_LAYER
rename=(DEPTH=D EARTH_LAYER=E));                                                                                                                             
if EARTH_LAYER ne E then DEPTH=coalesce(D,DEPTH);
drop D E;
run;

[farshidowrang]

Thank you my friend

Best regards

Farshid