BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
XiaoGuaiShou
Obsidian | Level 7

 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
1 ACCEPTED SOLUTION

Accepted Solutions
snoopy369
Barite | Level 11

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

1 REPLY 1
snoopy369
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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