BookmarkSubscribeRSS Feed
zana
Calcite | Level 5

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.

4 REPLIES 4
jwillis
Quartz | Level 8

Hi,

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

sudi
Calcite | Level 5

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;

SKK
Calcite | Level 5 SKK
Calcite | Level 5

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;

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 820 views
  • 0 likes
  • 5 in conversation