Help using Base SAS procedures

Counting Occurrences of a Variable and Deleting Records Based on Count

Reply
N/A
Posts: 0

Counting Occurrences of a Variable and Deleting Records Based on Count

Wow! What a long subject line! But I think it actually captures exactly what I am trying to do. I am a beginner, so please bear with me.

I have a dataset where one of the variables is 'SupervisorName'. What I would like to be able to do is count the number of times that a particular supervisor name occurs and delete all records which contain a supervisor name that doesn't appear more than 4 times in the dataset.

Example Dataset (this data has already been read into a SAS dataset):

Jim 3 4
Jim 4 5
Jim 6 7
Jim 6 8
Jim 3 2
Suzy 3 1
Suzy 2 1

Given this data, I want to delete all records which have Suzy as the supervisor since she occurs less than 4 times. Leaving only observations where Jim is the supervisor. This is just example data, the dataset I will be performing this on has hundreds of observations.

Thanks you so much for the help!
-jeff
Super Contributor
Posts: 291

Re: Counting Occurrences of a Variable and Deleting Records Based on Count

Here's one way ...

data Test;
input Name $1-4 Number Value;
datalines;
Jim 3 4
Jim 4 5
Jim 6 7
Jim 6 8
Jim 3 2
Suzy 3 1
Suzy 2 1
;
run;

proc freq data=Test;
table Name/out=SuprvCnt;
run;

proc sort data=test;
by name;
run;


data Test2;
merge Test (in=a)
SuprvCnt (in=b where=(Count >4));
by Name;
if b;
run;
PROC Star
Posts: 1,564

Re: Counting Occurrences of a Variable and Deleting Records Based on Count

This is compact and easy to read. What out for 2-pass sql performance on large volumes.
[pre]
data t;
input name $ a b;
cards;
Jim 3 4
Jim 4 5
Jim 6 7
Jim 6 8
Jim 3 2
Suzy 3 1
Suzy 2 1
run;
[/pre][pre]
proc sql;
create table t1(drop=c) as
select *, count(name) as c
from t
group by name
having c >4 ;
quit;
[/pre]
Ask a Question
Discussion stats
  • 2 replies
  • 555 views
  • 0 likes
  • 3 in conversation