Select specific date data from two tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Select specific date data from two tables

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

TESTDATEMEASURMENT
IE15154921347312-04-09249
IE15154921347313-04-09219
IE15154921347314-04-09222
IE15154921347315-04-09317
IE15154921347316-04-09270
IE15154921347324-05-09250
IE15154921347325-05-09195
IE15154921347326-05-09244
IE15154921347327-05-09248
IE15154921347328-05-09231
IE15154921347316-08-09276
IE15154921347317-08-09194
IE15154921347318-08-09273
IE15154921347319-08-09213
IE15154921347320-08-09311
IE15154921347306-09-09272
IE15154921347307-09-09298
IE15154921347308-09-09328
IE15154921347309-09-09385
IE15154921347310-09-09359

IDTESTDATE
IE15154921347303-02-09
IE15154921347310-02-09
IE15154921347317-02-09
IE15154921347324-02-09
IE15154921347302-03-09
IE15154921347311-03-09
IE15154921347324-03-09
IE15154921347331-03-09
IE15154921347316-04-09
IE15154921347321-04-09
IE15154921347319-05-09
IE15154921347326-05-09
IE15154921347310-06-09
IE15154921347323-06-09
IE15154921347308-07-09
IE15154921347314-07-09
IE15154921347321-07-09
IE15154921347328-07-09
IE15154921347304-08-09
IE15154921347306-08-09
IE15154921347311-08-09
IE15154921347318-08-09
IE15154921347325-08-09
IE15154921347327-08-09
IE15154921347308-09-09
IE15154921347315-09-09
IE15154921347322-09-09
IE15154921347329-09-09
IE15154921347306-10-09
IE15154921347313-10-09
IE15154921347320-10-09
IE15154921347327-10-09
IE15154921347303-11-09
IE15154921347310-11-09
IE15154921347317-11-09
IE15154921347325-11-09

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

Attachment
Attachment

Accepted Solutions
Solution
‎09-13-2012 08:51 AM
Super Contributor
Posts: 1,636

Re: Select specific date data from two tables

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;

View solution in original post


All Replies
Solution
‎09-13-2012 08:51 AM
Super Contributor
Posts: 1,636

Re: Select specific date data from two tables

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 277 views
  • 0 likes
  • 2 in conversation