Dear programmers,
I'm drilling many wells (defined in the column WELL_NAME) through the earth different layers. If I hit a layer, I like keep the name in a new column (LAYER_NAME2) until I reach the next layer. However, if I change the well (new WELL_NAME) then the value in the column LAYER_NAME2 should be empty until I hit the first layer in the next WELL.
What I have is below:
WELL_NAME | DEPTH | LAYER_NAME1 |
A | 0 | |
A | 100 | |
A | 200 | |
A | 300 | XU |
A | 400 | |
A | 500 | |
A | 600 | |
A | 700 | |
A | 800 | XT |
A | 900 | |
A | 1000 | |
A | 1100 | VX |
A | 1200 | |
A | 1300 | |
A | 1400 | |
A | 1500 | |
B | 0 | |
B | 100 | |
B | 200 | LT |
B | 300 | |
B | 400 | |
B | 500 | |
B | 600 | |
B | 700 | XV |
B | 800 | |
B | 900 | |
B | 1000 | |
B | 1100 | |
B | 1200 | |
B | 1300 | LY |
B | 1400 | |
C | 0 | |
C | 2000 | |
C | 4000 | NAA |
C | 6000 | |
C | 8000 | |
C | 10000 | |
C | 12000 | |
C | 14000 | |
C | 16000 | ST |
C | 18000 | |
C | 20000 | AZ |
C | 22000 | |
C | 24000 | |
D | 0 | |
D | 500 | |
D | 1000 | |
D | 1500 | MU |
D | 2000 | |
D | 2500 | |
D | 3000 | MY |
D | 3500 | FT |
D | 4000 | |
D | 4500 | |
D | 5000 | |
D | 5500 | |
D | 6000 | |
D | 6500 | LA |
D | 7000 | |
D | 7500 | |
D | 8000 |
What I want is the new column (LAYER_NAME2):
WELL_NAME | DEPTH | LAYER_NAME1 | LAYER_NAME2 |
A | 0 | ||
A | 100 | ||
A | 200 | ||
A | 300 | XU | XU |
A | 400 | XU | |
A | 500 | XU | |
A | 600 | XU | |
A | 700 | XU | |
A | 800 | XT | XT |
A | 900 | XT | |
A | 1000 | XT | |
A | 1100 | VX | VX |
A | 1200 | VX | |
A | 1300 | VX | |
A | 1400 | VX | |
A | 1500 | VX | |
B | 0 | ||
B | 100 | ||
B | 200 | LT | LT |
B | 300 | LT | |
B | 400 | LT | |
B | 500 | LT | |
B | 600 | LT | |
B | 700 | XV | XV |
B | 800 | XV | |
B | 900 | XV | |
B | 1000 | XV | |
B | 1100 | XV | |
B | 1200 | XV | |
B | 1300 | LY | LY |
B | 1400 | LY | |
C | 0 | ||
C | 2000 | ||
C | 4000 | NAA | NAA |
C | 6000 | NAA | |
C | 8000 | NAA | |
C | 10000 | NAA | |
C | 12000 | NAA | |
C | 14000 | NAA | |
C | 16000 | ST | ST |
C | 18000 | ST | |
C | 20000 | AZ | AZ |
C | 22000 | AZ | |
C | 24000 | AZ | |
D | 0 | ||
D | 500 | ||
D | 1000 | ||
D | 1500 | MU | MU |
D | 2000 | MU | |
D | 2500 | MU | |
D | 3000 | MY | MY |
D | 3500 | FT | FT |
D | 4000 | FT | |
D | 4500 | FT | |
D | 5000 | FT | |
D | 5500 | FT | |
D | 6000 | FT | |
D | 6500 | LA | LA |
D | 7000 | LA | |
D | 7500 | LA | |
D | 8000 | LA |
Observe that no layers exist when DEPTH=0
Can you please help with this?
Thank you very much in advance!
Best regards
Farshid Owrang
Let the RETAIN statement do it's thing!
data want;
length layer_name2 $ 3;
retain layer_name2;
set have;
by well_name;
if first.well_name or not missing(layer_name1)
then layer_name2=layer_name1;
run;
Try
data want;
do until(last.well_name);
set have;
by well_name;
length LAYER_NAME2 $10;
if LAYER_NAME1>' ' then LAYER_NAME2=LAYER_NAME1;
output;
end;
run;
Let the RETAIN statement do it's thing!
data want;
length layer_name2 $ 3;
retain layer_name2;
set have;
by well_name;
if first.well_name or not missing(layer_name1)
then layer_name2=layer_name1;
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.