topic Re: sql count records in SAS Procedures

sql count records

Barkamih — Mon, 08 Apr 2019 17:24:03 GMT

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;

Re: sql count records

novinosrin — Mon, 08 Apr 2019 17:30:47 GMT

do you mean you need

group by herd, Year ?

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

Re: sql count records

Barkamih — Mon, 08 Apr 2019 17:38:21 GMT

yes,

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

let see !

Re: sql count records

Tom — Mon, 08 Apr 2019 17:56:33 GMT

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;

Re: sql count records

Barkamih — Mon, 08 Apr 2019 18:10:21 GMT

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

Re: sql count records

novinosrin — Mon, 08 Apr 2019 18:14:54 GMT

@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

Re: sql count records

Barkamih — Mon, 08 Apr 2019 18:26:25 GMT

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 !!!