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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.