count in new column

Occasional Learner
Posts: 1

count in new column

I am trying to add a new column to a table that would
count the occurrences of a field change

I.e.:  field1 = 5    new column count = 1. Next record field1= 5
new column count = 2 , next record  field1 = 8
new column count  = 1

Is it possible to hold prior row value to test?

StuR

feild1new column
51
52
81
101
102
Frequent Contributor
Posts: 115

Re: count in new column

data have;

input field;

datalines;

5

5

8

10

10

;

data want;

set have;

by field;

if first.field then New_column=0;

New_column+1;

run;

Contributor
Posts: 52

Re: count in new column

A quick and dirty solution, especially if your data is not sorted.

First, let me simulate a dataset you may have.

/*************************/
/*** simulated dataset ***/
/*************************/
data have;
do i = 1 to 10000;
field1 = int(20*ranuni(3));  *assume that field1 is not greater than, say, 19;
output;
end;
run;

/******************/
/*** a solution ***/
/******************/
data want(keep= field1 zCnt);
array zCount(0:20) _temporary_;
set have;
zCount(field1)+1;
zCnt= zCount(field1);
run;

The first 30 rows of table want:
field1    zCnt

11        1
18        1
3        1
15        1
15        2
18        2
12        1
11        2
2        1
3        2
9        1
11        3
11        4
4        1
17        1
17        2
16        1
1        1
18        3
13        1
1        2
4        2
13        2
17        3
8        1
10        1
18        4
15        3
14        1
2        2

Posts: 3,186

Re: count in new column

not sure if this is what OP asked, but if it is, I really like the approach. To make more dynamic, here is Hash alternative:

data have;

do i = 1 to 10000;

field1 = int(20*ranuni(3));

*assume that field1 is not greater than, say, 19;

output;

end;

run;

data want;

if _n_=1 then

do;

declare hash h();

h.definekey('field1');

h.definedata('zcnt');

h.definedone();

call missing(zcnt);

end;

set have;

rc=h.find();

if rc=0 then

do;

zcnt+1;

h.replace();

end;

else

do;

zcnt=1;

h.replace();

end;

run;

Discussion stats
• 3 replies
• 615 views
• 0 likes
• 4 in conversation