topic Re: Adapting/taking values from the next row in SAS Programming

Adapting/taking values from the next row

farshidowrang — Thu, 19 Dec 2019 09:06:48 GMT

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

Re: Adapting/taking values from the next row

s_lassen — Thu, 19 Dec 2019 09:38:46 GMT

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;

Re: Adapting/taking values from the next row

ed_sas_member — Thu, 19 Dec 2019 10:05:02 GMT

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;

Re: Adapting/taking values from the next row

farshidowrang — Thu, 19 Dec 2019 10:44:54 GMT

Seemingly it works!

Thank you very much my friend!

Best regards

Farshid

Re: Adapting/taking values from the next row

Ksharp — Thu, 19 Dec 2019 11:40:30 GMT

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;

Re: Adapting/taking values from the next row

farshidowrang — Fri, 20 Dec 2019 15:37:33 GMT

Thank you my friend

Best regards

Farshid