DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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,

 

Smiley WinkThe 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;

 

Smiley SadThe result I have:

 accttot
Sum
state19300
ca
nc8440
sc3520
va17151

 

 

Smiley Very HappyThe result I want:

 accttot
Sum
state19300
ca
nc8440
va17151

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 ]
Posted in reply to XiaoGuaiShou

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;

View solution in original post


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 ]
Posted in reply to XiaoGuaiShou

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.

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

Discussion stats
  • 1 reply
  • 147 views
  • 2 likes
  • 2 in conversation