Select specific date data from two tables

greenhousemilk — Thu, 13 Sep 2012 08:40:26 GMT

I have these two kinds of separate file which contains same ID and different testdate. I want to select from the table two that has exactly same ID and testdate (which I have done quite easily) AND the date which is up-to 1 wk after the measurement. In fact I need ID and testdate (upto 7 days from measurement) to extract detail information from second table for further analysis.

Any suggesstions, thank you. I prefer code on proc sql.

ID	TESTDATE	MEASURMENT
IE151549213473	12-04-09	249
IE151549213473	13-04-09	219
IE151549213473	14-04-09	222
IE151549213473	15-04-09	317
IE151549213473	16-04-09	270
IE151549213473	24-05-09	250
IE151549213473	25-05-09	195
IE151549213473	26-05-09	244
IE151549213473	27-05-09	248
IE151549213473	28-05-09	231
IE151549213473	16-08-09	276
IE151549213473	17-08-09	194
IE151549213473	18-08-09	273
IE151549213473	19-08-09	213
IE151549213473	20-08-09	311
IE151549213473	06-09-09	272
IE151549213473	07-09-09	298
IE151549213473	08-09-09	328
IE151549213473	09-09-09	385
IE151549213473	10-09-09	359

ID	TESTDATE
IE151549213473	03-02-09
IE151549213473	10-02-09
IE151549213473	17-02-09
IE151549213473	24-02-09
IE151549213473	02-03-09
IE151549213473	11-03-09
IE151549213473	24-03-09
IE151549213473	31-03-09
IE151549213473	16-04-09
IE151549213473	21-04-09
IE151549213473	19-05-09
IE151549213473	26-05-09
IE151549213473	10-06-09
IE151549213473	23-06-09
IE151549213473	08-07-09
IE151549213473	14-07-09
IE151549213473	21-07-09
IE151549213473	28-07-09
IE151549213473	04-08-09
IE151549213473	06-08-09
IE151549213473	11-08-09
IE151549213473	18-08-09
IE151549213473	25-08-09
IE151549213473	27-08-09
IE151549213473	08-09-09
IE151549213473	15-09-09
IE151549213473	22-09-09
IE151549213473	29-09-09
IE151549213473	06-10-09
IE151549213473	13-10-09
IE151549213473	20-10-09
IE151549213473	27-10-09
IE151549213473	03-11-09
IE151549213473	10-11-09
IE151549213473	17-11-09
IE151549213473	25-11-09

PS: attached first table measurement.csv 2nd table detail.csv

Re: Select specific date data from two tables

Linlin — Thu, 13 Sep 2012 12:51:00 GMT

example:

data one;
input id testdate ddmmyy8. measurment;
format testdate ddmmyy8.;
cards;
1 12-04-09 20
1 13-04-09 30
1 24-05-09 40
;
proc print data=one;

data two;
input id testdate ddmmyy8. ;
format testdate ddmmyy8.;
cards;
1 12-04-09
1 13-04-09
1 18-04-09
1 24-05-09
1 30-05-09
;
proc sql;
create table want as select
     a.id,a.testdate as testdate_two, b.testdate as testdate_one,measurment
     from two as a,one as b
    where a.id=b.id and a.testdate between b.testdate and b.testdate+7;
quit;
proc print; run;

topic Select specific date data from two tables in SAS Programming

Select specific date data from two tables

Re: Select specific date data from two tables