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 |
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.