Filtering Data

[Rodolfo_Mingoti]

Hi, I would like help to filter my database. My database contains 1000 observations. The input data are:

OBS	COW_ID	TREATMENT	BULL	PREGNANCY
2	Cow_2	B	BULL_D	1
3	Cow_3	C	BULL_B	1
4	Cow_4	B	BULL_C	0
5	Cow_5	C	BULL_H	1
6	Cow_6	A	BULL_F	0
7	Cow_7	C	BULL_H	1
8	Cow_8	B	BULL_B	1
9	Cow_9	A	BULL_D	1
10	Cow_10	A	BULL_B	1

Would like to remove bulls who has frequency less than 50 observations, that is, keep only the bulls with frequency higher than 50 observations.

My strategy was to use PROC FREQ and find bulls with frequencies below 50 observations. The next step was to use the IF/THEN/DELETE statements to filter the database manually.

I wonder if anyone has an alternative idea to reduce manual labor and make it automatic.

I share the database and the procedures that I used, to understand the steps by steps.

/*----------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------*/
proc freq data=work.bull_diagnosis order=freq;
table Treatment Bull / nopercent nocol nocum ;
run;
/*----------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------*/
Data Bull_filtered;
set work.bull_diagnosis;

     if BULL = "BULL_A"  then delete;
     if BULL = "BULL_G" then delete;
     if BULL = "BULL_J"  then delete;
     if BULL = "BULL_I"   then delete;
     if BULL = "BULL_E" then delete;

run;
quit;
/*----------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------*/
proc freq data=work.bull_filtered order=freq;
table Treatment Bull / nopercent nocol nocum ;
run;
/*----------------------------------------------------------------------------*/

[Ksharp · Accepted solution]

proc sql;
create table want as
select * from work.bull_diagnosis
group by bull
having count(*)>50 ;
quit;

[Rodolfo_Mingoti]

Thank you very much Ksharp ... it worked perfectly!
The problem was solved!!!