topic Re: Marking an intervall starting from minimum the value of a specific column in SAS Programming

Marking an intervall starting from minimum the value of a specific column

farshidowrang — Thu, 24 Oct 2019 12:41:28 GMT

Dear programmer friends

I have a dataset according to below. It shows the reservoir depth. For each WELLBORE, the reservoir starts at the minimum TOP_LAYER_DEPTH. For example, the reservoir starts at 5 for WELLBORE A.

WELLBORE	DEPTH	TOP_LAYER_DEPTH
A	0
A	1
A	2
A	3
A	4
A	5	5
A	6
A	7
A	8
A	9	9
A	10
A	11
A	12	12
A	13
B	0
B	1
B	2	2
B	3
B	4
B	5	5
B	6
B	7	7
B	8
C	0
C	1
C	2
C	3
C	4	4
C	5
C	6
C	7
C	8
C	9	9
C	10

I like to mark where the depth starts for each WELLBORE. I like to create a flag (INSIDE_RESERVOIR_FLG) to mark this interval. I want to have such table as below:

WELLBORE_NAME	DEPTH	TOP_LAYER_DEPTH	INSIDE_RESERVOIR_FLG
A	0		0
A	1		0
A	2		0
A	3		0
A	4		0
A	5	5	1
A	6		1
A	7		1
A	8		1
A	9	9	1
A	10		1
A	11		1
A	12	12	1
A	13		1
B	0		0
B	1		0
B	2	2	1
B	3		1
B	4		1
B	5	5	1
B	6		1
B	7	7	1
B	8		1
C	0		0
C	1		0
C	2		0
C	3		0
C	4	4	1
C	5		1
C	6		1
C	7		1
C	8		1
C	9	9	1
C	10		1

Can you please help me with it?

Thank you very much in advance!

Best regards

Farshid

Re: Marking an intervall starting from minimum the value of a specific column

PaigeMiller — Thu, 24 Oct 2019 12:43:26 GMT

data want;
    set have;
    by wellbore_name;
    retain flag;
    if first.wellbore_name then flag=0;
    if not missing(top_layer_depth) then flag=1;
run;

Re: Marking an intervall starting from minimum the value of a specific column

Kurt_Bremser — Thu, 24 Oct 2019 12:45:39 GMT

Just a retained variable, and by-group processing:

data want;
set have;
by wellbore;
retain inside_reservoir_flg;
if first.wellbore then inside_reservoir_flg = 0;
if top_layer_depth ne . then inside_reservoir_flg = 1;
run;

Re: Marking an intervall starting from minimum the value of a specific column

novinosrin — Thu, 24 Oct 2019 13:20:36 GMT

Hi @farshidowrang


data have;
infile cards expandtabs truncover;
input WELLBORE $	DEPTH	TOP_LAYER_DEPTH;
cards;
A	0	 
A	1	 
A	2	 
A	3	 
A	4	 
A	5	5
A	6	 
A	7	 
A	8	 
A	9	9
A	10	 
A	11	 
A	12	12
A	13	 
B	0	 
B	1	 
B	2	2
B	3	 
B	4	 
B	5	5
B	6	 
B	7	7
B	8	 
C	0	 
C	1	 
C	2	 
C	3	 
C	4	4
C	5	 
C	6	 
C	7	 
C	8	 
C	9	9
C	10	 
;
data want;
 if 0 then set have;
 do INSIDE_RESERVOIR_FLG=0 by 0 until(last.WELLBORE);
  set have;
  by WELLBORE;
  if TOP_LAYER_DEPTH then INSIDE_RESERVOIR_FLG=1;
  output;
 end;
run;

Re: Marking an intervall starting from minimum the value of a specific column

farshidowrang — Thu, 24 Oct 2019 15:05:21 GMT

Dear Friend

I don't think it is working well

The inside_reservoir_flg shold stop producing "1" when the Wellbore is changing

I don't thin your code do it

Best regards

Farshid

Re: Marking an intervall starting from minimum the value of a specific column

farshidowrang — Thu, 24 Oct 2019 15:28:38 GMT

WoW!!

It works body!!

Thank you very much man!

Best regards

Farshid

Re: Marking an intervall starting from minimum the value of a specific column

farshidowrang — Thu, 24 Oct 2019 15:29:48 GMT

Thank you very much Kurt!

Best regards

Farshid

Re: Marking an intervall starting from minimum the value of a specific column

Kurt_Bremser — Thu, 24 Oct 2019 19:28:28 GMT

@farshidowrang wrote:

Dear Friend

I don't think it is working well

The inside_reservoir_flg shold stop producing "1" when the Wellbore is changing

I don't thin your code do it

Best regards

Farshid

Have you tested it?

Re: Marking an intervall starting from minimum the value of a specific column

hashman — Thu, 24 Oct 2019 21:59:00 GMT

@farshidowrang:

With the availability of the SUM statement, no RETAIN and/or superfluous IF logic are needed:

data have ;                                                                                                                             
  input wellbore $ depth top_layer_depth ;                                                                                              
  cards ;                                                                                                                               
A   0  .                                                                                                                                
A   1  .                                                                                                                                
A   2  .                                                                                                                                
A   3  .                                                                                                                                
A   4  .                                                                                                                                
A   5  5                                                                                                                                
A   6  .                                                                                                                                
A   7  .                                                                                                                                
A   8  .                                                                                                                                
A   9  9                                                                                                                                
A  10  .                                                                                                                                
A  11  .                                                                                                                                
A  12 12                                                                                                                                
A  13  .                                                                                                                                
B   0  .                                                                                                                                
B   1  .                                                                                                                                
B   2  2                                                                                                                                
B   3  .                                                                                                                                
B   4  .                                                                                                                                
B   5  5                                                                                                                                
B   6  .                                                                                                                                
B   7  7                                                                                                                                
B   8  .                                                                                                                                
C   0  .                                                                                                                                
C   1  .                                                                                                                                
C   2  .                                                                                                                                
C   3  .                                                                                                                                
C   4  4                                                                                                                                
C   5  .                                                                                                                                
C   6  .                                                                                                                                
C   7  .                                                                                                                                
C   8  .                                                                                                                                
C   9  9                                                                                                                                
C  10  .                                                                                                                                
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  set have ;                                                                                                                            
  by wellbore ;                                                                                                                         
  inside_reservoir_flg + N (top_layer_depth) ;                                                                                          
  if last.wellbore then inside_reservoir_flg = 0 ;                                                                                      
run ;

Or, amending @novinosrin's code to take advantage of auto-setting the non-retained flag variable to missing before the DoW-loop:

data want ;                                                                                                                             
  do until (last.wellbore) ;                                                                                                            
    set have ;                                                                                                                          
    by wellbore ;                                                                                                                       
    inside_reservoir_flg = sum (inside_reservoir_flg, N (top_layer_depth)) ;                                                            
    output ;                                                                                                                            
  end ;                                                                                                                                 
run ;

Kind regards

Paul D.