BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

2 REPLIES 2
ballardw
Super User

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
Rhodochrosite | Level 12

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;

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 951 views
  • 2 likes
  • 3 in conversation