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
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;
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;
What would you want if the Erath_layer group only has one entry?
@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".
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.