topic Re: count in new column in SAS Enterprise Guide

count in new column

Srich — Mon, 29 Dec 2014 18:09:34 GMT

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?

Thanks in advance

StuR

feild1	new column
5	1
5	2
8	1
10	1
10	2

Re: count in new column

naveen_srini — Mon, 29 Dec 2014 18:44:12 GMT

data have;

input field;

datalines;

;

data want;

set have;

by field;

if first.field then New_column=0;

New_column+1;

run;

Re: count in new column

billfish — Mon, 29 Dec 2014 18:52:14 GMT

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

Re: count in new column

Haikuo — Mon, 29 Dec 2014 20:01:03 GMT

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;