How to count the number of rows with a data step with a specific order

[yahoo0806]

Hi all,

Would you mind let me know what your thoughts are if I want to count for below dataset: column id, order, and type are the dataset I currently have, and column count is the dataset I want to create. Basically, I want to count the type variable within each id following the order, i.e., if the one type got interrupted by another type, then the second part of the same type need to be re-counted for this id. 

id	order	type	count
1	1	Not_Applicable	1
1	2	Not_Applicable	2
1	3	Not_Applicable	3
1	4	Not_Applicable	4
1	5	Not_Applicable	5
1	6	Not_Applicable	6
1	7	Not_Applicable	7
1	8	Not_Applicable	8
1	9	Not_Applicable	9
1	10	Not_Applicable	10
1	11	Not_Applicable	11
1	12	Not_Applicable	12
1	13	Not_Applicable	13
1	14	Not_Applicable	14
1	15	Not_Applicable	15
1	16	Not_Applicable	16
1	17	Not_Applicable	17
1	18	Not_Applicable	18
1	19	intermittent	1
1	20	Not_Applicable	1
2	1	Not_Applicable	1
2	2	Not_Applicable	2
2	3	Not_Applicable	3
2	4	Not_Applicable	4
2	5	Not_Applicable	5
2	6	Not_Applicable	6
2	7	Not_Applicable	7
2	8	Not_Applicable	8
2	9	Not_Applicable	9
2	10	Not_Applicable	10
2	11	Not_Applicable	11
2	12	Not_Applicable	12
2	13	Not_Applicable	13
2	14	Discontinued	1
2	15	Discontinued	2
2	16	Discontinued	3
2	17	Discontinued	4
2	18	Discontinued	5
2	19	Discontinued	6
2	20	Discontinued	7
3	1	Not_Applicable	1
3	2	Not_Applicable	2
3	3	Not_Applicable	3
3	4	Not_Applicable	4
3	5	Not_Applicable	5
3	6	Not_Applicable	6
3	7	Not_Applicable	7
3	8	Not_Applicable	8
3	9	Not_Applicable	9
3	10	Not_Applicable	10
3	11	intermittent	1
3	12	intermittent	2
3	13	intermittent	3
3	14	intermittent	4
3	15	intermittent	5
3	16	Not_Applicable	1
3	17	Not_Applicable	2
3	18	Not_Applicable	3
3	19	Not_Applicable	4
3	20	Not_Applicable	5

[ballardw]

See if this come close:

data want;
   set have;
   by id order type notsorted;
   retain count;
   if first.id or first.type the count=1;
   else count+1;
run;

[yahoo0806]

no, it's not working, i got count  = 1 for every row.

[ballardw]

---

@yahoo0806 wrote:

no, it's not working, i got count  = 1 for every row.

---

Try with out ORDER on the BY statement.

Couldn't test the code as no data step supplied and too lazy at the moment to turn your "data" into one.

[PeterClemmensen]

This should do it.

data have;
input id order type $20.;
datalines;
1 1  Not_Applicable 
1 2  Not_Applicable 
1 3  Not_Applicable 
1 4  Not_Applicable 
1 5  Not_Applicable 
1 6  Not_Applicable 
1 7  Not_Applicable 
1 8  Not_Applicable 
1 9  Not_Applicable 
1 10 Not_Applicable 
1 11 Not_Applicable 
1 12 Not_Applicable 
1 13 Not_Applicable 
1 14 Not_Applicable 
1 15 Not_Applicable 
1 16 Not_Applicable 
1 17 Not_Applicable 
1 18 Not_Applicable 
1 19 intermittent   
1 20 Not_Applicable 
2 1  Not_Applicable 
2 2  Not_Applicable 
2 3  Not_Applicable 
2 4  Not_Applicable 
2 5  Not_Applicable 
2 6  Not_Applicable 
2 7  Not_Applicable 
2 8  Not_Applicable 
2 9  Not_Applicable 
2 10 Not_Applicable 
2 11 Not_Applicable 
2 12 Not_Applicable 
2 13 Not_Applicable 
2 14 Discontinued   
2 15 Discontinued   
2 16 Discontinued 
2 17 Discontinued 
2 18 Discontinued 
2 19 Discontinued 
2 20 Discontinued 
3 1  Not_Applicable 
3 2  Not_Applicable 
3 3  Not_Applicable 
3 4  Not_Applicable 
3 5  Not_Applicable 
3 6  Not_Applicable 
3 7  Not_Applicable 
3 8  Not_Applicable 
3 9  Not_Applicable 
3 10 Not_Applicable 
3 11 intermittent   
3 12 intermittent   
3 13 intermittent   
3 14 intermittent   
3 15 intermittent   
3 16 Not_Applicable 
3 17 Not_Applicable 
3 18 Not_Applicable 
3 19 Not_Applicable 
3 20 Not_Applicable 
;

data want;
   set have;
   by id type notsorted;
   if first.type then count = 0;
   count + 1;
run;