join table by id

[sasphd]

Hello, 

I want to join table by id

Table A

id date            price

1  20170101    10

1 20170102      12

2  20180401     8

2   20180402    5

Table B

id date            price

1  20170103    15

2  20180405     4

want A and B

id date            price

1  20170101    10

1 20170102      12

1  20170103    15

2  20180401     8

2   20180402    5

2  20180405     4

[ballardw · Accepted solution]

There are at least a half dozen ways to do this.

If you data sets are actually sorted by the Id and date and there are not any of the same dates in A and B then

data want; 
   merge A 
         B
   ;
   by id date;
run;

is about the simplest.

If you have duplicates of Date for a given Id then you may want:

data want;
   set A
        B
   ;
run;

proc sort data=want;
   by id date;
run;

[PhilC]

This isn't a join, as far as I understand SQL notation and syntax.  This can be achieved in SQL using a UNION but it requires another pass to get the order correct.  But SAS can interleave the records; I was taught the term collate, I digress.  The datasets A and B I presume are properly sorted.

data want;
  set a b;
    by id date;
run;