Cases with at least four observations

[MattvanPallen]

Hello again!

I would like to ask you how to get from "have" to "wanna" base.

Have:

Company, Year,

A,2001

A,2002

A,2003

A,2004

B,2001

B,2002

B,2003

C,2001

C,2002

C,2003

C,2004

Wanna:

Company, Year,

A,2001

A,2002

A,2003

A,2004

C,2001

C,2002

C,2003

C,2004

I know the logic, but cannot really figure out the code...

I should assign e.g. item for every observation and delete each company which has item number <4

Thank you in advance!

[RW9]

Well, few ways to do it in SQL, heres one:

proc sql;

  create table want as

  select company,year

  from  (

          select  *

                  ,count(1) as tmp

          from    have

          group by company

        )

  where tmp=4;

quit;

[slchen]

data have;
input Company $ Year;
cards;
A 2001
A 2002
A 2003
A 2004
B 2001
B 2002
B 2003
C 2001
C 2002
C 2003
C 2004
;
run;
proc sql;
select * from have group by company having count(*)=4;
quit;

[Ksharp · Accepted solution]

select * from have group by company having count(distinct year)=4; 

Xia Keshan