DATA Step, Macro, Functions and more

How to count by subgroup?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

View solution in original post


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?

Posted in reply to KurtBremser

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 159 views
  • 0 likes
  • 3 in conversation