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 (which may repeat several times) shall be duplicated.
Observe 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 | 12200 | K |
A | 12300 | K |
A | 12400 | K |
A | 12500 | Z |
A | 12600 | Z |
A | 12900 | Z |
B | 24000 | E |
B | 24100 | E |
B | 24200 | E |
B | 24500 | L |
B | 24900 | L |
B | 25000 | L |
B | 25100 | K |
B | 25200 | K |
B | 25400 | K |
C | 3500 | E |
C | 3600 | E |
C | 3700 | K |
C | 3800 | K |
C | 3900 | K |
C | 4000 | Z |
C | 4100 | Z |
C | 4800 | Z |
C | 4900 | E |
C | 5000 | E |
C | 5100 | Z |
C | 5200 | Z |
C | 5201 | K |
C | 5202 | Z |
C | 5203 | Z |
C | 5204 | K |
C | 5205 | Z |
And what I really need to have is the following table. Note the italic bold values inside the table. These are the new duplicated rows:
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 |
C | 5205 | Z |
D | 33200 | L |
an you please help me with this problem?
Best regards
Farshid Owrang
You can do this:
- step 1: create a table with the "dupkeys" = output the last observation for each new layer
- step 2 : concatenate the two datasets.
data dupkey;
set have;
by WELLBORE_NAME ERATH_LAYER notsorted;
if last.ERATH_LAYER then output;
run;
data want;
set have dupkey;
run;
proc sort data=want;
by WELLBORE_NAME DEPTH ERATH_LAYER;
run;
You can do this:
- step 1: create a table with the "dupkeys" = output the last observation for each new layer
- step 2 : concatenate the two datasets.
data dupkey;
set have;
by WELLBORE_NAME ERATH_LAYER notsorted;
if last.ERATH_LAYER then output;
run;
data want;
set have dupkey;
run;
proc sort data=want;
by WELLBORE_NAME DEPTH ERATH_LAYER;
run;
I would be very tempted to try
data dupkey; set have; by WELLBORE_NAME ERATH_LAYER notsorted; if last.ERATH_LAYER then output; output; 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.