Marking an interval showing the start, the inside and the end of the interval

[farshidowrang]

Dear programmers,

I send many wellbores through the different layers of the earth like a snake.

I like to know when my wellbore starts the TOP of a layer, when it is INSIDE the layer and when it reaches the BOTTOM of the layer.

My dataset is:

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
C	5205	Z
D	16000	L
D	16200	L
D	16400	K
D	16600	L
D	16800	L

What I need to have is:

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

My code is not working properly:

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

data WANT;
set HAVE;
by  WELLBORE_NAME DEPTH ERATH_LAYER;

length LOCATION $20;
if first.ERATH_LAYER=1 then LOCATION="TOP";
else if first.ERATH_LAYER=0 then LOCATION="INSIDE";
if last.ERATH_LAYER=1 then LOCATION="BOTTOM";
run;

Can you please help me with this?

Thank you very much in advance!

Best regards

Farshid Owrang 

[s_lassen · Accepted solution]

You should not use DEPTH as a BY variable. Instead, use the NOTSORTED option:

data WANT;
  set HAVE;
  by  WELLBORE_NAME ERATH_LAYER notsorted; 
  length LOCATION $20;
  if first.ERATH_LAYER then LOCATION="TOP";
  else if last.ERATH_LAYER then LOCATION="BOTTOM";
  else LOCATION="INSIDE";
run;

[farshidowrang]

Thank you my friend!

best regards

Farshid

[farshidowrang]

Thank you my friend!

it works!

[ballardw]

What would you want if the Erath_layer group only has one entry?

[farshidowrang]

I think it does not happen

The answer is: I don't know!

[ballardw]

---

@farshidowrang wrote:
I think it does not happen

The answer is: I don't know!

---

It is a good idea to consider such things. I see that your apparent depth is recorded at multiples of 100. So if one of the segments you are dealing with only had an affective change less than 100 it may not appear in your data. The code that @s_lassen provided will assign 'Top'. So you may want to look for summary counts, if you do any such for reporting, where Top for a layer is greater than Bottom.

I deal with Family data for some of my work. And when certain problems came up with report preparation I discovered Children under age 5 that did not belong to any family, or belonged to multiple families at the same time. And people that have a date recorded for ending an activity before it started. Neither of which should ever happen.

 "I think it does not happen" should be verified to "it has not happened".