Dear programmers,
I send many wellbores through the different layers of the earth like a snake. Sometimes some of the wellbores go back to the same layer again. Let me explain more:
When they stay in the same layer it is ok. The problem arises when a wellbore goes to another layer and then it goes back to the previous layer or a layer it has already been there. I like to count how many times a wellbore goes back to the same layer or layers.
Observe when the name of the wellbore (WELLBORE_NAME) changes:
My dataset is:
WELLBORE_NAME |
DEPTH |
ERATH_LAYER |
A |
11900 |
E1 Top |
A |
12000 |
E2 Top |
A |
12100 |
E3 Top |
A |
12200 |
K1 Top |
A |
12300 |
K2 Top |
A |
12400 |
K3 Top |
A |
12500 |
Z1 Top |
A |
12600 |
Z2 Top |
A |
12700 |
Z3 Top |
A |
12800 |
Z2 Top |
A |
12900 |
Z1 Top |
A |
13000 |
K3 Top |
A |
13100 |
K2 Top |
A |
13200 |
K1 Top |
A |
13200 |
E3 Top |
A |
13200 |
E2 Top |
A |
13200 |
E1 Top |
B |
24000 |
E1 Top |
B |
24100 |
E1 Top |
B |
24200 |
E1 Top |
B |
24300 |
E2 Top |
B |
24400 |
E2 Top |
B |
24500 |
E3 Top |
B |
24600 |
E3 Top |
B |
24700 |
E3 Top |
B |
24800 |
E2 Top |
B |
24900 |
E2 Top |
B |
25000 |
E1 Top |
B |
25100 |
E1 Top |
B |
25200 |
E2 Top |
B |
25300 |
E2 Top |
B |
25400 |
E1 Top |
C |
3500 |
E1 Top |
C |
3600 |
E2 Top |
C |
3700 |
K2 Top |
C |
3800 |
K2 Top |
C |
3900 |
K4 Top |
C |
4000 |
Z1 Top |
C |
4100 |
Z1 Top |
C |
4200 |
Z1 Top |
C |
4300 |
Z1 Top |
C |
4400 |
Z1 Top |
C |
4500 |
Z2 Top |
C |
4600 |
Z1 Top |
C |
4700 |
Z2 Top |
C |
4800 |
Z1 Top |
C |
4900 |
E2 Top |
C |
5000 |
E1 Top |
C |
5100 |
Z1 Top |
C |
5200 |
Z1 Top |
C |
5201 |
K4 Top |
C |
5202 |
Z1 Top |
C |
5203 |
Z1 Top |
C |
5204 |
K4 Top |
C |
5205 |
Z1 Top |
What I need to have is a new column so called COUNTING_DISCRET_VARIABLE like this:
WELLBORE_NAME |
DEPTH |
ERATH_LAYER |
COUNTING_DISCRET_VARIABLE |
A |
11900 |
E1 Top |
1 |
A |
12000 |
E2 Top |
1 |
A |
12100 |
E3 Top |
1 |
A |
12200 |
K1 Top |
1 |
A |
12300 |
K2 Top |
1 |
A |
12400 |
K3 Top |
1 |
A |
12500 |
Z1 Top |
1 |
A |
12600 |
Z2 Top |
1 |
A |
12700 |
Z3 Top |
1 |
A |
12800 |
Z2 Top |
2 |
A |
12900 |
Z1 Top |
2 |
A |
13000 |
K3 Top |
2 |
A |
13100 |
K2 Top |
2 |
A |
13200 |
K1 Top |
2 |
A |
13200 |
E3 Top |
2 |
A |
13200 |
E2 Top |
2 |
A |
13200 |
E1 Top |
2 |
B |
24000 |
E1 Top |
1 |
B |
24100 |
E1 Top |
1 |
B |
24200 |
E1 Top |
1 |
B |
24300 |
E2 Top |
1 |
B |
24400 |
E2 Top |
1 |
B |
24500 |
E3 Top |
1 |
B |
24600 |
E3 Top |
1 |
B |
24700 |
E3 Top |
1 |
B |
24800 |
E2 Top |
2 |
B |
24900 |
E2 Top |
2 |
B |
25000 |
E1 Top |
2 |
B |
25100 |
E1 Top |
2 |
B |
25200 |
E2 Top |
3 |
B |
25300 |
E2 Top |
3 |
B |
25400 |
E1 Top |
3 |
C |
3500 |
E1 Top |
1 |
C |
3600 |
E2 Top |
1 |
C |
3700 |
K2 Top |
1 |
C |
3800 |
K2 Top |
1 |
C |
3900 |
K4 Top |
1 |
C |
4000 |
Z1 Top |
1 |
C |
4100 |
Z1 Top |
1 |
C |
4200 |
Z1 Top |
1 |
C |
4300 |
Z1 Top |
1 |
C |
4400 |
Z1 Top |
1 |
C |
4500 |
Z2 Top |
1 |
C |
4600 |
Z1 Top |
2 |
C |
4700 |
Z2 Top |
2 |
C |
4800 |
Z1 Top |
3 |
C |
4900 |
E2 Top |
2 |
C |
5000 |
E1 Top |
2 |
C |
5100 |
Z1 Top |
4 |
C |
5200 |
Z1 Top |
4 |
C |
5201 |
K4 Top |
2 |
C |
5202 |
Z1 Top |
4 |
C |
5203 |
Z1 Top |
4 |
C |
5204 |
K4 Top |
3 |
C |
5205 |
Z1 Top |
5 |
Can you please help me with this?
Best regards
Farshid Owrang
data have;
input WELLBORE_NAME $1. DEPTH ERATH_LAYER & $10.;
cards;
A 11900 E1 Top
A 12000 E2 Top
A 12100 E3 Top
A 12200 K1 Top
A 12300 K2 Top
A 12400 K3 Top
A 12500 Z1 Top
A 12600 Z2 Top
A 12700 Z3 Top
A 12800 Z2 Top
A 12900 Z1 Top
A 13000 K3 Top
A 13100 K2 Top
A 13200 K1 Top
A 13200 E3 Top
A 13200 E2 Top
A 13200 E1 Top
B 24000 E1 Top
B 24100 E1 Top
B 24200 E1 Top
B 24300 E2 Top
B 24400 E2 Top
B 24500 E3 Top
B 24600 E3 Top
B 24700 E3 Top
B 24800 E2 Top
B 24900 E2 Top
B 25000 E1 Top
B 25100 E1 Top
B 25200 E2 Top
B 25300 E2 Top
B 25400 E1 Top
C 3500 E1 Top
C 3600 E2 Top
C 3700 K2 Top
C 3800 K2 Top
C 3900 K4 Top
C 4000 Z1 Top
C 4100 Z1 Top
C 4200 Z1 Top
C 4300 Z1 Top
C 4400 Z1 Top
C 4500 Z2 Top
C 4600 Z1 Top
C 4700 Z2 Top
C 4800 Z1 Top
C 4900 E2 Top
C 5000 E1 Top
C 5100 Z1 Top
C 5200 Z1 Top
C 5201 K4 Top
C 5202 Z1 Top
C 5203 Z1 Top
C 5204 K4 Top
C 5205 Z1 Top
;
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H () ;
h.definekey ("ERATH_LAYER") ;
h.definedata ("count") ;
h.definedone () ;
end;
do until(last.WELLBORE_NAME);
set have;
by WELLBORE_NAME ERATH_LAYER notsorted ;
if first.ERATH_LAYER then do;
if h.find() ne 0 then count=1;
else count=sum(count,1);
h.replace();
end;
output;
end;
h.clear();
run;
At this point @farshidowrang, you have asked enough questions and we have helped you enough so that I think you need to help us now.
Post data according to the following instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
We would be much happier when you do this.
data have;
input WELLBORE_NAME $1. DEPTH ERATH_LAYER & $10.;
cards;
A 11900 E1 Top
A 12000 E2 Top
A 12100 E3 Top
A 12200 K1 Top
A 12300 K2 Top
A 12400 K3 Top
A 12500 Z1 Top
A 12600 Z2 Top
A 12700 Z3 Top
A 12800 Z2 Top
A 12900 Z1 Top
A 13000 K3 Top
A 13100 K2 Top
A 13200 K1 Top
A 13200 E3 Top
A 13200 E2 Top
A 13200 E1 Top
B 24000 E1 Top
B 24100 E1 Top
B 24200 E1 Top
B 24300 E2 Top
B 24400 E2 Top
B 24500 E3 Top
B 24600 E3 Top
B 24700 E3 Top
B 24800 E2 Top
B 24900 E2 Top
B 25000 E1 Top
B 25100 E1 Top
B 25200 E2 Top
B 25300 E2 Top
B 25400 E1 Top
C 3500 E1 Top
C 3600 E2 Top
C 3700 K2 Top
C 3800 K2 Top
C 3900 K4 Top
C 4000 Z1 Top
C 4100 Z1 Top
C 4200 Z1 Top
C 4300 Z1 Top
C 4400 Z1 Top
C 4500 Z2 Top
C 4600 Z1 Top
C 4700 Z2 Top
C 4800 Z1 Top
C 4900 E2 Top
C 5000 E1 Top
C 5100 Z1 Top
C 5200 Z1 Top
C 5201 K4 Top
C 5202 Z1 Top
C 5203 Z1 Top
C 5204 K4 Top
C 5205 Z1 Top
;
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H () ;
h.definekey ("ERATH_LAYER") ;
h.definedata ("count") ;
h.definedone () ;
end;
do until(last.WELLBORE_NAME);
set have;
by WELLBORE_NAME ERATH_LAYER notsorted ;
if first.ERATH_LAYER then do;
if h.find() ne 0 then count=1;
else count=sum(count,1);
h.replace();
end;
output;
end;
h.clear();
run;
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.