Cases with at least four observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Cases with at least four observations

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!


Accepted Solutions
Solution
‎05-13-2014 09:45 AM
Super User
Posts: 10,023

Re: Cases with at least four observations

Posted in reply to MattvanPallen
select * from have group by company having count(distinct year)=4; 

Xia Keshan

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Cases with at least four observations

Posted in reply to MattvanPallen

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;

Super Contributor
Posts: 275

Re: Cases with at least four observations

Posted in reply to MattvanPallen

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;

Solution
‎05-13-2014 09:45 AM
Super User
Posts: 10,023

Re: Cases with at least four observations

Posted in reply to MattvanPallen
select * from have group by company having count(distinct year)=4; 

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 168 views
  • 6 likes
  • 4 in conversation