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
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.