Duplicating the last row

[farshidowrang]

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

[ed_sas_member · Accepted solution]

Hi @farshidowrang 

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;

[farshidowrang]

Thank you very much my friend!

Can you please help me with my other questions too?

Best regards

Farshid

[ballardw]

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;

[farshidowrang]

Seemingly, it works too!!!

[farshidowrang]

I'm using this code now!