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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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
  • 759 views
  • 2 likes
  • 3 in conversation