## How to count by subgroup?

Solved
Occasional Contributor
Posts: 5

# How to count by subgroup?

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

Accepted Solutions
Solution
‎11-27-2017 10:56 AM
Super User
Posts: 10,280

## Re: How to count by subgroup?

``````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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎11-27-2017 10:56 AM
Super User
Posts: 10,280

## Re: How to count by subgroup?

``````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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

## Re: How to count by subgroup?

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?

Super User
Posts: 10,280

## Re: How to count by subgroup?

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 @draycut's example data step for how to post data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,283

## Re: How to count by subgroup?

``````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;``````
Occasional Contributor
Posts: 5

## Re: How to count by subgroup?

Thanks everyone!

☑ This topic is solved.