topic Re: PROC SQL in SAS Programming

PROC SQL

Barkamih — Wed, 05 Dec 2018 22:25:45 GMT

Hi all

I have this code working correctly to get 4 records of each COW_ID by using all my dataset that contained 10 years of records, but I need to get 4 records for each cow_id for each year.

my data called have

I have the variable YEAR in this data

So How I can to specify my requirement by using this code?

best regards

barkamih

PROC SQL;
create table want as
select *
from have
group by COW_ID
having count(*) > 3  ;
RUN;

Re: PROC SQL

novinosrin — Wed, 05 Dec 2018 22:29:38 GMT

PROC SQL;
create table want as
select *
from have
group by COW_ID, year
having count(*) > 3  ;
RUN;

group by COW_ID, year

Re: PROC SQL

PGStats — Wed, 05 Dec 2018 22:32:08 GMT

group by year, COW_ID

Re: PROC SQL

novinosrin — Wed, 05 Dec 2018 22:34:45 GMT

Good catch

Re: PROC SQL

PGStats — Wed, 05 Dec 2018 22:39:47 GMT

Not a catch at all. I hadn't seen your submission before I posted. Both will give the same result, but sorted differently.

And both will be wrong if OP wanted cow_id's with 4 records in every year...

Re: PROC SQL

novinosrin — Wed, 05 Dec 2018 22:41:03 GMT

Oh Sir, I think you comprehended -", but I need to get 4 records for each cow_id for each year. " this correctly

Re: PROC SQL

Barkamih — Wed, 05 Dec 2018 22:50:20 GMT

both of you are correct, the first reply get accepted and the second one got like, I'm trying to be equal for both of you hahaha, I hope I did.

I appreciate your response

thanks million my friends

Re: PROC SQL

novinosrin — Wed, 05 Dec 2018 22:54:59 GMT

It's all good. Just a thought, I think it;s better to end proc sql with quit; than run;