sql count records

[Barkamih]

Hi all,

I have created this code in order to keep at least 5 cow_id for each herd per year. 

this code working perfectly across all years, 

what I need,  How to specify my requirement per year not across all years?  

data have has these three variables   cow_id,  herd and year. 

Regards 

proc sql;
create table herdcount as
select * from have
where herd in 
(select distinct herd from have group by herd having count(distinct cow_id) >= 5);
run;quit;

[novinosrin]

do you mean you need

group by herd, Year   ?

(select distinct herd from have group by herd,year having count(distinct cow_id) >= 5);

[Barkamih]

yes, 

so that I just forgot to put year in the proper order. 

let see !

[Barkamih]

I changed the code like this but it does not work, 

and this my data looks like this, 

proc sql;
create table herdcount as
select * from have
where year herd in 
(select distinct herd from have group by year herd having count(distinct cow_id) >= 5);
run;quit;

 cow_id       herd     year 

1	1	2000
2	1	2000
3	1	2000
4	1	2000
5	1	2000
6	1	2000
7	1	2000
8	1	2000
9	2	2000
10	2	2000
11	2	2000
12	2	2000
1	1	2001
2	1	2001
3	1	2001
4	1	2001

[novinosrin · Accepted solution]

@Barkamih   Are you after this?

data have;
input cow_id       herd     year ;
cards;
1	1	2000
2	1	2000
3	1	2000
4	1	2000
5	1	2000
6	1	2000
7	1	2000
8	1	2000
9	2	2000
10	2	2000
11	2	2000
12	2	2000
1	1	2001
2	1	2001
3	1	2001
4	1	2001
;

proc sql;
create table want as
select *
from have
group by herd, year
having count(distinct cow_id)>=5;
quit;

Please post your expected output sample too 

[Tom]

Do you want the HERD*YEAR combinations with at least 5 cows?

The HERDs that have at least 5 cows in ANY year?

The HERDs that have at least 5 cows in ALL years for that HERD?

Here is an answer for that last question (although it does not put any constraints on the numbers of years of data for that HERD).

proc sql;
create table big_herds as
select * from have
where herd in 
  (select herd from 
    (select herd,year,count(distinct cow_id) as ncows 
    from have
    group by herd,year
    )
    group by herd
    having 5<=min(ncows)
  )
;
quit;

[Barkamih]

yes, this is my question? 

The HERDs that have at least 5 cows in ANY year?

for example,  keep 5 cows or more for any herd and delete 4 cows or less per year

I submitted your edits but still something wrong !!!