How to count by subgroup?

[schoi]

Here is my example dataset:

fruit apple  

fruit apple  

fruit orange

fruit orange 

fruit orange

vegetable celery

vegetable broccoli 

meat beef

meat chicken

How do I create a third column, that identifies the group number in column 1?

The resulting dataset should look like this

fruit apple  1

fruit apple  1

fruit orange 1

fruit orange 1

fruit orange 1

vegetable celery 2

vegetable broccoli 2

meat beef 3

meat chicken 3

[Kurt_Bremser · Accepted solution]

data want;
set have;
by col1 notsorted;
retain subgroup 0;
if first.col1 then subgroup + 1;
run;

You might consider sorting by col1 first, so you can omit the notsorted option and make sure that a certain subgroup does not receive more than one number.

[schoi]

Thanks!

I have other string columns in my RETAIN statement.  It doesn't seem to work if I put those columns in front of subgroup 0.  Anyway around this?

[Kurt_Bremser]

---

@schoi wrote:

Thanks!

I have other string columns in my RETAIN statement.  It doesn't seem to work if I put those columns in front of subgroup 0.  Anyway around this?

---

In order for in-depth help, post your code (with log if code fails with ERROR or WARNING) and some example data to test the code against. See @PeterClemmensen's example data step for how to post data.

[PeterClemmensen]

data have;
input food$ type$;
datalines;
fruit apple  
fruit apple  
fruit orange
fruit orange 
fruit orange
vegetable celery
vegetable broccoli 
meat beef
meat chicken
;

proc sort data=have;
	by food;
run;

data want;
	set have;
	by food;
	if first.food then groupnum+1;
run;

[schoi]

Thanks everyone!