Desktop productivity for business analysts and programmers

count in new column

Reply
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?

Thanks in advance

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

Respected Advisor
Posts: 3,156

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;

Ask a Question
Discussion stats
  • 3 replies
  • 566 views
  • 0 likes
  • 4 in conversation