topic Re: How to count the number of rows with a data step with a specific order in SAS Programming

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

yahoo0806 — Mon, 25 Jan 2021 21:22:12 GMT

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

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

ballardw — Mon, 25 Jan 2021 22:14:31 GMT

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;

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

yahoo0806 — Mon, 25 Jan 2021 21:50:41 GMT

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

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

ballardw — Mon, 25 Jan 2021 22:16:13 GMT

@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.

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

PeterClemmensen — Tue, 26 Jan 2021 07:03:45 GMT

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;