topic Re: join table by id in SAS Procedures

join table by id

sasphd — Fri, 05 Feb 2021 18:56:40 GMT

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

Re: join table by id

ballardw — Fri, 05 Feb 2021 19:01:50 GMT

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;

Re: join table by id

PhilC — Fri, 05 Feb 2021 19:05:53 GMT

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;