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

XiaoGuaiShou — Tue, 02 Jan 2018 19:53:51 GMT

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

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

snoopy369 — Tue, 02 Jan 2018 20:06:47 GMT

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;

topic Counting the number of observations in a BY-Group/ proc tabulate in SAS Programming

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

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