Proc sql - join on relative dates

chris2377 — Wed, 25 Sep 2019 12:10:36 GMT

EDIT: the code at the end works, I've just made a mistake in the example. Thanks @yabwon for pointing this out

Hi,

I have two datasets (ds1 and ds2), each of them containg date variable. Now I want to join them using the condition that date in ds2 is less than 30 days before or 30 days after the date in ds1. In the example below, in the outcome dataset (want) observations for B are not matched as there are more than 30 days between 2018-12-25 and 2019-04-20. For A and C the condition is met, so the observations from ds2 should be added.

data ds1;
	input date :yymmdd10. id $2. var1;
	format date yymmdd10.;
	datalines;
	2019-03-31 A 10
	2018-12-25 B 20
	2019-06-15 C 30
	;
run;

data ds2;
	input date :yymmdd10. id $2. var2;
	format date yymmdd10.;
	datalines;
	2019-02-25 A 15
	2019-04-20 B 25
	2019-07-10 C 35
	;
run;

data want;
	input date :yymmdd10. id :$2. var1 var2;
	format date yymmdd10.;
	datalines;
	2019-02-25 A 10 15
	2019-04-20 B 20 .
	2019-07-10 C 30 35
	;
run;

I've tried the following code, but values for id=A are not matched while they should be (there is less than 30 days between 2019-02-25 and 2019-03-31). C is mergin properly. What am I doing wrong?

proc sql;
	create table merge as
	select * from ds1 A
	left join ds2 B
	on A.id=B.id and B.date < A.date+30 and B.date> A.date-30;
quit;

Best regards,

Chris

Re: Proc sql - join on relative dates

yabwon — Wed, 25 Sep 2019 12:02:56 GMT

Comparison:

ods listing;
data test;
	input date :yymmdd10. id $2. date1 :yymmdd10.;
	format date date1 yymmdd10.;
  diff = date - date1;
	datalines;
	2019-03-31 A 2019-02-25
	2018-12-25 B 2019-04-20
	2019-06-15 C 2019-07-10
	;
run;
proc print;
run;

returns:

Obs          date    id         date1    diff

 1     2019-03-31    A     2019-02-25      34
 2     2018-12-25    B     2019-04-20    -116
 3     2019-06-15    C     2019-07-10     -25

All the best

Bart

Re: Proc sql - join on relative dates

chris2377 — Wed, 25 Sep 2019 12:09:04 GMT

@yabwon All clear now, thanks!

topic Proc sql - join on relative dates in SAS Programming

Proc sql - join on relative dates

Re: Proc sql - join on relative dates

Re: Proc sql - join on relative dates