Date:         Sun, 19 Jun 2005 13:51:15 -0700
Reply-To:     Arjen <a.benedictus@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arjen <a.benedictus@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Subtracting values from different tables by ID
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

Hi guys,

I'm working on a dairy herd disease model. I have two tables (see
below), one table with calving info and one with sampling info. For
each sample taken, I'd like to determine how many days the sampling
date is away from the last calving of that cow. In my example, the
result for cow #10 (only one sample) would be 50 days. For cow 11,
results would be 190 days (calving at 11750) and 90 days (calving at
12140).

I tried something like:
proc sql; create table DIM as
select distinct t1.*,case
when t1.DamID=t2.DamID then t1.Sample_Date-t2.DOB
end as DIM
from Sampling as t1 left join (select * from Calving) as t2 on 1
group by DamID;
quit;
But this doesn't seem to work...

Any ideas? Gracias!

Arjen


Data Calving;
input DamID DOB;
label DOB=Birth Date;
cards;
10 10900
11 11750
11 12050
12 12000
12 12365
;
run;

Data Sampling;
input DamID Sample_Date Result $;
cards;
10 10950 N
11 11740 N
11 11940 N
11 12140 P
12 12100 N
12 12300 P
;
run;