SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Is it possible to merge data by a range?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Is it possible to merge data by a range?

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;

 

 


Accepted Solutions
Solution
‎12-01-2015 06:56 AM
Super User
Super User
Posts: 7,994

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

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;

View solution in original post


All Replies
Solution
‎12-01-2015 06:56 AM
Super User
Super User
Posts: 7,994

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

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;
New Contributor
Posts: 4

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

Hi,
thank you, that helped a lot!
Community Manager
Posts: 567

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

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 291 views
  • 1 like
  • 3 in conversation