BookmarkSubscribeRSS Feed
Sotarkadin
Calcite | Level 5

Good Day all,

 

I currently have two tables I am trying to join to do data validations between a source of record system and the end system for it.

Currently Table A has account, date, and amount.  Table B has account date and amount as well where as the account and amounts "should match" the date can vary because of the system lag and processing until it's updated into the system.  I am trying to find the first instance of the matching account and dollar amount in Table B that happens between each period of time in the rows of Table A.

 

I have an example of each table below and what the expected result should be.  Table A has a record for each month with the amount posted.  Depending on updates to the system, table B has multiple entries as it records every time a change is made.  I am only looking to find the first instance after the date from Table A, which as you can see in the results Table, our first date in table A is 2/22/2019, and our second is 3/22/2019.  For Table B I want to find the first date that occurs after 2/22 but only if it occurs before 3/22 for the matching amount in table A.  I hope I have provided enough information.

 

Table A Table B
account Date Amount account Date Amount
12345 2/22/2019 5000 12345 2/23/2019 5000
12345 3/22/2019 5000 12345 2/24/2019 5000
12345 4/22/2019 4800 12345 3/1/2019 5000
12345 5/22/2019 4600 12345 3/23/2019 5000
12345 6/22/2019 4500 12345 5/12/2019 4800
      12345 5/23/2019 4600
      12345 5/30/2019 4600
      12345 6/22/2019 4500

 

Result
account Amount Date_A Date_B
12345 5000 2/22/2019 2/23/2019
12345 5000 3/22/2019 3/23/2019
12345 4800 4/22/2019 5/12/2019
12345 4600 5/22/2019 5/23/2019
12345 4500 6/22/2019 6/22/2019
3 REPLIES 3
ChrisNZ
Tourmaline | Level 20

>I hope I have provided enough information.

I think you did.

What's missing is the code to generate the data to save us time and confusion.

See How to convert datasets to data steps

Sotarkadin
Calcite | Level 5

Thank you for the infromation.  I will work on generating the code because of currently I haven't written anything as those two tables are on our database and I didn't know where to begin.

PaigeMiller
Diamond | Level 26

The request from @ChrisNZ was not to see your code, the request was to provide the sample data in a usable form for us, that usable form is a SAS data step and can be created by the instructions at the link he gave.

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 553 views
  • 0 likes
  • 3 in conversation