topic Re: Retrieve first instance of date between dates located in another table in SAS Programming

Retrieve first instance of date between dates located in another table

Sotarkadin — Tue, 06 Aug 2019 20:06:44 GMT

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

Re: Retrieve first instance of date between dates located in another table

ChrisNZ — Tue, 06 Aug 2019 23:42:55 GMT

>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

Re: Retrieve first instance of date between dates located in another table

Sotarkadin — Wed, 07 Aug 2019 12:31:44 GMT

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.

Re: Retrieve first instance of date between dates located in another table

PaigeMiller — Wed, 07 Aug 2019 12:36:17 GMT

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.