Help using Base SAS procedures

Keeping years that passed the critical point

Reply
Frequent Contributor
Posts: 81

Keeping years that passed the critical point

Hi everyone!

I’m trying to solve this:

  • I have a data set with three variables. Every ID has a year (2000 to 2002) but they have unlimited on Have data set, fortunately the IDs in this data set can’t have different year. For example: year 2000 appears 7 times on data set with three different IDs like the example below.

What i need to do is to remove the years with less than 2 different IDs. Say for instance my data set looks like the following:

ID

Obs

Year

1

12

2000

1

10

2000

2

17

2001

2

23

2001

3

35

2002

3

68

2002

3

54

2002

4

46

2000

4

61

2000

4

53

2000

4

32

2000

5

26

2001

6

14

2000

Output should be like this:

ID

Obs

Year

1

12

2000

1

10

2000

4

46

2000

4

61

2000

4

53

2000

4

32

2000

6

14

2000

Any help would be greatly appreciated.

Regular Contributor
Posts: 217

Re: Keeping years that passed the critical point

Hi,

Based on your example, would 2001 be included in your final output?  Would 2002 be excluded from your final output?

Occasional Contributor
Posts: 5

Re: Keeping years that passed the critical point

data one;

input id obs year;

datalines;

1 12 2000

1 10 2000

2 17 2001

2 23 2001

3 35 2002

3 68 2002

3 54 2002

4 46 2000

4 61 2000

4 53 2000

4 32 2000

5 26 2001

6 14 2000

;

proc sql;

select * from one where year=2000;

quit;

run;

Contributor SKK
Contributor
Posts: 35

Re: Keeping years that passed the critical point

Hi Zana,

Considering the case as you have mentioned below:

 fortunately the IDs in this data set can’t have different year.

Each ID will have distinctly one year only, In this scenario below code will help for you:

PROC SQL;

CREATE TABLE WANT AS

  SELECT A.ID, OBS, YEAR FROM ONE AS A INNER JOIN

      (SELECT ID FROM (SELECT DISTINCT ID, YEAR FROM ONE)

       GROUP BY YEAR HAVING COUNT(YEAR) GT 2) AS B

    ON A.ID=B.ID;

QUIT;

Super User
Posts: 10,035

Re: Keeping years that passed the critical point

It is good to solve this kind problem by SQL.

proc sql;

create table want as

  select *

   from one

    group by year

  having count(distinct id) gt 2;

quit;

Xia Keshan

Ask a Question
Discussion stats
  • 4 replies
  • 213 views
  • 0 likes
  • 5 in conversation