Date:         Wed, 16 Feb 2011 15:45:59 +0100
Reply-To:     Keith Larson <keith.larson@ZOOEKOL.LU.SE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Keith Larson <keith.larson@ZOOEKOL.LU.SE>
Subject:      Using PROC SQL to create duplicate datasets with different
              criteria
Content-Type: text/plain; charset=ISO-8859-1

Dear list,

I am trying to find and create three different duplicates datasets I
can use for repeatability analysis. I would like following datasets
that include both all records meeting the following criteria:

1. Duplicate ID within the same year. Note in this case the lab is
always the same.
2. Duplicate ID where year is different and lab is the same.
3. Duplicate ID where year is different and lab is different.

This is my attempt so far (that does not work), but I am absolutely
stumped on how to move forward. I realized I will probably have to do
this in three steps.

proc sql;
  create table Replicates as
  select * from Original
  group by ID, Year, Lab having count(*)>1
  order by ID, Year;
quit;

Sample data:

data original;
input ID Year Lab;
datalines;
1 1 1
1 1 1
2 2 2
2 2 2
3 1 1
3 2 1
4 1 1
4 2 2
4 2 2
5 1 1
5 1 2
6 1 1
6 1 2
7 1 1
7 2 1
8 1 1
9 2 1
10 1 1
10 1 2
11 1 1
11 1 2
12 1 1
12 1 2
12 2 2
;

Cheers,
Keith

**************************************
Keith Larson, PhD Student
Animal Ecology, Lund University
Sölvegatan 37
223 62 LUND
Sweden
Phone: +46 (0)46 2229014
Mobile: +46 (0)73 0465016
Fax: +46 (0)46 2224716
Skype: sternacaspia
FB: keith.w.larson@gmail.com
**************************************