BookmarkSubscribeRSS Feed
Anna_nag
Obsidian | Level 7

Hi All,

 

I am struggling to join two table without creating duplicate rows using proc sql ( not sure if any other method is more efficient).

Inner join is on: datepart(table1.date)=datepart(table2.date) AND tag=tag AND ID=ID

 

I think the problem is date and different names in table 1. By just looking that the table its clear that table1's row 1 should be joined with table 2's row 1 because the transaction started at 00:04 in table one and finished at 00:06 in table 2. I issue I am having is I cant join on dates with the timestamp so I am removing timestamps and because of that its creating duplicates.

 

 

Table1:

id tag    date            amount   name_x
1 23      01JUL2018:00:04  12          smith ltd
1 23      01JUL2018:00:09  12          anna smith

 

table 2:

 

id tag  ref   amount   date
1 23   19   12          01JUL2018:00:06:00
1 23   20   12          01JUL2018:00:10:00

 

Desired output:

id tag    date            amount   name_x       ref
1 23      01JUL2018  12          smith ltd       19
1 23      01JUL2018  12          anna smith   20

 

Appreciate your help.

4 REPLIES 4
Reeza
Super User

So what is the rule then to join?

You will get duplicates so how do you know which ones to keep and which ones not to keep?


In a simple example like this you could use a MERGE without a BY statement but I suspect that won't generalize to your actual data. 

 

 

Anna_nag
Obsidian | Level 7

Timestamp is the rule.. table 1 row1 transaction always occurs before table 2 row1 and should be joined but I am struggling on how to do that.

Reeza
Super User

Or another way to phrase it, the datetime in table 2 is the smallest time that is greater than the datetime in table1? Will this hold for other cases? Can you provide several more data points and expected output to ensure the logic is correct?

Anna_nag
Obsidian | Level 7

Yes that will hold the logic. Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2056 views
  • 0 likes
  • 2 in conversation