topic Re: Is it possible to merge data by a range? in SAS Data Management

Is it possible to merge data by a range?

Gundi — Mon, 09 Nov 2015 14:58:03 GMT

Hello,

I have two datasets (testa and testb) which I like to merge by the date-values . But I can not merge by the date-variable itself because sometimes the dates differ by some days but should be written to the same observation. Therfore I want to introduce a range according to the date of the first dataset und merge the information of the second dataset only if that date fits into that range. On the bottomline I try to solve this problem:

data testa;

input nr date_a date_low date_up int $@@;

cards;

1 3 1 5 e

1 5 3 7 ee

2 4 2 6 f

3 8 6 10 g

4 8 6 10 h

4 13 11 15 i

;run;

data testb;

input nr date_b empf $@@;

cards;

1 2 a

1 2 b

3 8 c

4 9 d

;run;

*This is what I want to get;

data testz;

input nr date_a date_low date_up int $ date_b empf $@@;

cards;

1 3 1 5 e 2 a

1 3 1 5 e 2 b

1 5 3 7 ee . .

2 4 2 6 f . .

3 8 6 10 g 8 c

4 8 6 10 h 9 d

4 13 11 15 i . .

;

run;

Re: Is it possible to merge data by a range?

RW9 — Mon, 09 Nov 2015 15:08:08 GMT

Hi,

Yes, just use <= with the range:

data testa;
  input nr date_a date_low date_up int $@@;
cards;
1 3 1 5 e
1 5 3 7 ee
2 4 2 6 f
3 8 6 10 g
4 8 6 10 h
4 13 11 15 i
;
run;
data testb;
  input nr date_b empf $@@;
cards;
1 2 a
1 2 b
3 8 c
4 9 d
;
run;
proc sql;
  create table WANT as
  select  A.*,
          B.DATE_B,
          B.EMPF
  from    WORK.TESTA A
  full join WORK.TESTB B
  on      A.NR=B.NR
  and     A.DATE_LOW <= B.DATE_B <= A.DATE_UP;
quit;

Re: Is it possible to merge data by a range?

Gundi — Fri, 27 Nov 2015 15:14:46 GMT

Hi,
thank you, that helped a lot!

Re: Is it possible to merge data by a range?

AnnaBrown — Mon, 30 Nov 2015 14:47:22 GMT

Hi Gundi,

I'm glad you found some useful info! If one of the replies was the exact solution to your problem, can you "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.

Thanks!

Anna