Counting discrete variable

[farshidowrang]

Dear programmers,

I send many wellbores through the different layers of the earth like a snake. Sometimes some of the wellbores go back to the same layer again. Let me explain more:

When they stay in the same layer it is ok. The problem arises when a wellbore goes to another layer and then it goes back to the previous layer or a layer it has already been there. I like to count how many times a wellbore goes back to the same layer or layers.

Observe when the name of the wellbore (WELLBORE_NAME) changes:

My dataset is:

WELLBORE_NAME	DEPTH	ERATH_LAYER
A	11900	E1 Top
A	12000	E2 Top
A	12100	E3 Top
A	12200	K1 Top
A	12300	K2 Top
A	12400	K3 Top
A	12500	Z1 Top
A	12600	Z2 Top
A	12700	Z3 Top
A	12800	Z2 Top
A	12900	Z1 Top
A	13000	K3 Top
A	13100	K2 Top
A	13200	K1 Top
A	13200	E3 Top
A	13200	E2 Top
A	13200	E1 Top
B	24000	E1 Top
B	24100	E1 Top
B	24200	E1 Top
B	24300	E2 Top
B	24400	E2 Top
B	24500	E3 Top
B	24600	E3 Top
B	24700	E3 Top
B	24800	E2 Top
B	24900	E2 Top
B	25000	E1 Top
B	25100	E1 Top
B	25200	E2 Top
B	25300	E2 Top
B	25400	E1 Top
C	3500	E1 Top
C	3600	E2 Top
C	3700	K2 Top
C	3800	K2 Top
C	3900	K4 Top
C	4000	Z1 Top
C	4100	Z1 Top
C	4200	Z1 Top
C	4300	Z1 Top
C	4400	Z1 Top
C	4500	Z2 Top
C	4600	Z1 Top
C	4700	Z2 Top
C	4800	Z1 Top
C	4900	E2 Top
C	5000	E1 Top
C	5100	Z1 Top
C	5200	Z1 Top
C	5201	K4 Top
C	5202	Z1 Top
C	5203	Z1 Top
C	5204	K4 Top
C	5205	Z1 Top

What I need to have is a new column so called COUNTING_DISCRET_VARIABLE like this:

WELLBORE_NAME	DEPTH	ERATH_LAYER	COUNTING_DISCRET_VARIABLE
A	11900	E1 Top	1
A	12000	E2 Top	1
A	12100	E3 Top	1
A	12200	K1 Top	1
A	12300	K2 Top	1
A	12400	K3 Top	1
A	12500	Z1 Top	1
A	12600	Z2 Top	1
A	12700	Z3 Top	1
A	12800	Z2 Top	2
A	12900	Z1 Top	2
A	13000	K3 Top	2
A	13100	K2 Top	2
A	13200	K1 Top	2
A	13200	E3 Top	2
A	13200	E2 Top	2
A	13200	E1 Top	2
B	24000	E1 Top	1
B	24100	E1 Top	1
B	24200	E1 Top	1
B	24300	E2 Top	1
B	24400	E2 Top	1
B	24500	E3 Top	1
B	24600	E3 Top	1
B	24700	E3 Top	1
B	24800	E2 Top	2
B	24900	E2 Top	2
B	25000	E1 Top	2
B	25100	E1 Top	2
B	25200	E2 Top	3
B	25300	E2 Top	3
B	25400	E1 Top	3
C	3500	E1 Top	1
C	3600	E2 Top	1
C	3700	K2 Top	1
C	3800	K2 Top	1
C	3900	K4 Top	1
C	4000	Z1 Top	1
C	4100	Z1 Top	1
C	4200	Z1 Top	1
C	4300	Z1 Top	1
C	4400	Z1 Top	1
C	4500	Z2 Top	1
C	4600	Z1 Top	2
C	4700	Z2 Top	2
C	4800	Z1 Top	3
C	4900	E2 Top	2
C	5000	E1 Top	2
C	5100	Z1 Top	4
C	5200	Z1 Top	4
C	5201	K4 Top	2
C	5202	Z1 Top	4
C	5203	Z1 Top	4
C	5204	K4 Top	3
C	5205	Z1 Top	5

Can you please help me with this?

Best regards

Farshid Owrang 

[PaigeMiller]

At this point @farshidowrang, you have asked enough questions and we have helped you enough so that I think you need to help us now.

Post data according to the following instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

We would be much happier when you do this.

[novinosrin · Accepted solution]

data have;
input WELLBORE_NAME	$1. DEPTH	ERATH_LAYER & $10.;
cards;
A	11900	E1 Top
A	12000	E2 Top
A	12100	E3 Top
A	12200	K1 Top
A	12300	K2 Top
A	12400	K3 Top
A	12500	Z1 Top
A	12600	Z2 Top
A	12700	Z3 Top
A	12800	Z2 Top
A	12900	Z1 Top
A	13000	K3 Top
A	13100	K2 Top
A	13200	K1 Top
A	13200	E3 Top
A	13200	E2 Top
A	13200	E1 Top
B	24000	E1 Top
B	24100	E1 Top
B	24200	E1 Top
B	24300	E2 Top
B	24400	E2 Top
B	24500	E3 Top
B	24600	E3 Top
B	24700	E3 Top
B	24800	E2 Top
B	24900	E2 Top
B	25000	E1 Top
B	25100	E1 Top
B	25200	E2 Top
B	25300	E2 Top
B	25400	E1 Top
C	3500	E1 Top
C	3600	E2 Top
C	3700	K2 Top
C	3800	K2 Top
C	3900	K4 Top
C	4000	Z1 Top
C	4100	Z1 Top
C	4200	Z1 Top
C	4300	Z1 Top
C	4400	Z1 Top
C	4500	Z2 Top
C	4600	Z1 Top
C	4700	Z2 Top
C	4800	Z1 Top
C	4900	E2 Top
C	5000	E1 Top
C	5100	Z1 Top
C	5200	Z1 Top
C	5201	K4 Top
C	5202	Z1 Top
C	5203	Z1 Top
C	5204	K4 Top
C	5205	Z1 Top
;


data want;
if _n_=1 then do;
 if 0 then set have;
   dcl hash H () ;
   h.definekey  ("ERATH_LAYER") ;
   h.definedata ("count") ;
   h.definedone () ;
 end;
 do until(last.WELLBORE_NAME);
  set have;
  by WELLBORE_NAME ERATH_LAYER notsorted ;
  if first.ERATH_LAYER then do;
  if h.find() ne 0 then count=1;
  else count=sum(count,1);
  h.replace();
  end;
  output;
 end;
 h.clear();
run;

[farshidowrang]

Apparently it works!!

Thank you very much my friend!

Best regards

Farshid