Create Row Number Column by Two Columns SAS

Question

I want to add a row number column to a SAS data set based on the values of two columns.

Type_1 and Type_2 columns are what I have and the Row Number is what I need.

Type_1  Type_2  Row Number
A       1       1
A       1       2
A       2       1
A       2       2
B       1       1
B       2       1
B       2       2
B       3       1
C       1       1
C       1       2
C       2       1
C       3       1
C       4       1
C       4       2

I have this code to count rows on one column value:

data work.want;
set work.have;
rownumber + 1;
by type_1 notsorted;
if first.type_1 then rownumber=1;
run;

But I don't know how to scale this to being able to group by multiple column criteria. I know that I can just concatenate type_1 and type_2 and the above code would work, but I would like to be able to do it without making a helper column. Is there any way to change the data step for it to work? Or is there another SAS function that I don't know of that can accomplish this?

Joe Joe · Accepted Answer · 2017-11-29T19:23:10

If you want to reset it on any change in either TYPE_1 or TYPE_2, then just use the last variable in the list; any change in an earlier variable will trigger a change in the FIRST variable.

data work.want;
  set work.have;
  by type_1 type_2 notsorted;
  rownumber + 1;
  if first.type_2 then rownumber=1;
run;

Create Row Number Column by Two Columns SAS

1 Answers