BookmarkSubscribeRSS Feed
Srich
Calcite | Level 5

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
3 REPLIES 3
naveen_srini
Quartz | Level 8

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;

billfish
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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