Filling down value in an interval

[farshidowrang]

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 

[novinosrin]

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;

[farshidowrang]

Thank you very much my friend!

Best regards

Farshid

[PaigeMiller · Accepted solution]

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;

[farshidowrang]

Thank you very much my friend!
Best regards
Farshid