topic How to count by subgroup? in SAS Programming

How to count by subgroup?

schoi — Mon, 27 Nov 2017 14:19:04 GMT

Here is my example dataset:

fruit apple

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 orange 1

vegetable celery 2

vegetable broccoli 2

meat beef 3

meat chicken 3

Re: How to count by subgroup?

Kurt_Bremser — Mon, 27 Nov 2017 14:25:32 GMT

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.

Re: How to count by subgroup?

schoi — Mon, 27 Nov 2017 14:48:27 GMT

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?

Re: How to count by subgroup?

PeterClemmensen — Mon, 27 Nov 2017 14:49:25 GMT

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;

Re: How to count by subgroup?

Kurt_Bremser — Mon, 27 Nov 2017 15:23:32 GMT

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

Re: How to count by subgroup?

schoi — Mon, 27 Nov 2017 15:57:15 GMT

Thanks everyone!