topic Counting Occurrences of a Variable and Deleting Records Based on Count in SAS Procedures

Counting Occurrences of a Variable and Deleting Records Based on Count

deleted_user — Fri, 22 May 2009 15:09:12 GMT

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

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

Bill — Fri, 22 May 2009 15:34:55 GMT

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;

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

ChrisNZ — Tue, 26 May 2009 21:33:38 GMT

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]