## Counting the number of observations in a BY-Group/ proc tabulate

Solved
Occasional Contributor
Posts: 14

# Counting the number of observations in a BY-Group/ proc tabulate

In proc tabulate, I don't want to show the result when count<2 group by state (for example,  like following, I don't want show "sc" since sc only have one record).

How to resolve this? Any help would be greatly appreciated.

Thanks,

The code I have:

``````data have;
input state \$ accttot;
datalines;
ca     7000
ca     6500
ca     5800
nc     4800
nc     3640
sc     3520
va     4490
va     8700
va     2850
va     1111
;

proc tabulate data=have;
class state;
var accttot;
table state,accttot;
run;``````

The result I have:

 accttot Sum state 19300 ca nc 8440 sc 3520 va 17151

The result I want:

 accttot Sum state 19300 ca nc 8440 va 17151

Accepted Solutions
Solution
‎01-02-2018 03:58 PM
Super Contributor
Posts: 320

## Re: Counting the number of observations in a BY-Group/ proc tabulate

[ Edited ]

I don't think there's a direct way to do that in PROC TABULATE. You'd need to do it in a previous step, or in a successor step.

One way to do that would be to include the N as a column, use TABULATE to output to a dataset, drop the N column and rows with N=1, then re-run tabulate to display the result.

Another way would be to set up a view of the data using BY group processing on STATE, count the rows with just one (where FIRST.state and LAST.state are both 1), and delete them, then tabulate off that dataset.

Example of the latter:

``````data have;
input state \$ accttot;
datalines;
ca     7000
ca     6500
ca     5800
nc     4800
nc     3640
sc     3520
va     4490
va     8700
va     2850
va     1111
;
run;

data have_V/view=have_v;
set have;
by state;
if first.state and last.state then delete;
run;

proc tabulate data=have_v;
class state;
var accttot;
table state,accttot;
run;``````

All Replies
Solution
‎01-02-2018 03:58 PM
Super Contributor
Posts: 320

## Re: Counting the number of observations in a BY-Group/ proc tabulate

[ Edited ]

I don't think there's a direct way to do that in PROC TABULATE. You'd need to do it in a previous step, or in a successor step.

One way to do that would be to include the N as a column, use TABULATE to output to a dataset, drop the N column and rows with N=1, then re-run tabulate to display the result.

Another way would be to set up a view of the data using BY group processing on STATE, count the rows with just one (where FIRST.state and LAST.state are both 1), and delete them, then tabulate off that dataset.

Example of the latter:

``````data have;
input state \$ accttot;
datalines;
ca     7000
ca     6500
ca     5800
nc     4800
nc     3640
sc     3520
va     4490
va     8700
va     2850
va     1111
;
run;

data have_V/view=have_v;
set have;
by state;
if first.state and last.state then delete;
run;

proc tabulate data=have_v;
class state;
var accttot;
table state,accttot;
run;``````
☑ This topic is solved.