topic Re: Reconcile Two Data Sets in SAS in SAS Programming

Reconcile Two Data Sets in SAS

iMaq_23 — Tue, 23 Jan 2018 21:40:12 GMT

Hi All,

I working on creating a program in SAS that reconciles two data sets by four variables: Date, Category, ID, and summation of Amount.

Data set one has a total amount and data set two has multiple amounts that when summed equal the total amount in data set one. In the summation I need the program to match the two data sets by ID, Category and Date.

The issue is the ID in data set one is slightly different than data set two; Data set one has 8 number ID and Data set two has 12 number ID. The leftmost 8 numbers match in both data sets.

I then need the program to export the results to an excel file.

Is there an easy way to write this? I have all the import steps complete, just need to write the reconciliation part? Is there a reconciliation function in SAS or Macro function that could do this?

Thanks,
iMaq_23

Re: Reconcile Two Data Sets in SAS

Astounding — Tue, 23 Jan 2018 21:51:42 GMT

It's relatively easy to create an extra variable in your second data set, one that matches on the first 8 characters of ID. How you do that would be different, depending on whether ID is numeric or character. For numeric:

data dataset2a;

set dataset2;

old_id = id;

id = int(id/10000);

run;

For character:

data dataset2a;

set dataset2;

old_id = id;

id = substr(id, 1, 8);

run;

Then you can sort and merge, since you have ID values that match.

Re: Reconcile Two Data Sets in SAS

iMaq_23 — Tue, 23 Jan 2018 21:59:00 GMT

I see that makes sense, but how about the summation? The ID's will match after that step, however, in data set two there are multiple rows that sum to one row in data set one. After that summation I would need the program to export to an excel: reconciled transactions, reconciled transactions, etc. Thanks.

Re: Reconcile Two Data Sets in SAS

ballardw — Tue, 23 Jan 2018 22:49:43 GMT

@iMaq_23 wrote:

I see that makes sense, but how about the summation? The ID's will match after that step, however, in data set two there are multiple rows that sum to one row in data set one. After that summation I would need the program to export to an excel: reconciled transactions, reconciled transactions, etc. Thanks.

Show what the expected output looks like. Best is to provide small example data sets in the form of data steps so that we can generate code to work with data that resembles yours. Then show the expected result for the given input data sets.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Reconcile Two Data Sets in SAS

iMaq_23 — Wed, 24 Jan 2018 14:16:25 GMT

Unfortunately I can't attach any documents. But I can give an example of the data set's I'm working with:

Data Set 1

Customer Name ID Amount Direction Date

Customer 1 1234567 1000 Outgoing 1/09/2018

Customer 1 1234567 1250 Incoming 1/10/2018

Customer 2 7645321 1000 Incoming 1/11/2018

Customer 2 7645321 1250 Outgoing 1/12/2018

Data Set 2

Date ID Customer Name Amount Direction

1/09/2018 12345670001 Customer 1 500 Outgoing

1/09/2018 12345670001 Customer 1 250 Outgoing

1/10/2018 12345670001 Customer 1 750 Incoming

1/10/2018 12345670001 Customer 1 250 Incoming

1/11/2018 76543210001 Customer 2 1000 Incoming

1/12/2018 76543210001 Customer 2 1250 Outgoing

I need the SAS program to reconcile these two tables by date, ID, Sum(Amount) and Direction; and then produce the results in an excel file with four tabs: Reconciled Outgoing, Reconciled Incoming, Un-reconciled Outgoing, Un-reconciled Incoming.

Thanks.

Re: Reconcile Two Data Sets in SAS

ballardw — Wed, 24 Jan 2018 15:48:58 GMT

And what does the actual result look like?

You have also introduced something you call "non-reconciled". What does that mean in terms of the data? Does "reconciled" mean that the sums match and non-reconciled mean that they do not match?

Re: Reconcile Two Data Sets in SAS

iMaq_23 — Wed, 24 Jan 2018 16:00:56 GMT

So the result should look as follows in an excel file:

Reconciled Outgoing Tab - Tab 1

Date Customer Name Customer ID Amount Direction

1/09/2018 Customer 1 1234567 1000 Outgoing

1/12/2018 Customer 2 7654321 1250 Outgoing

Reconciled Incoming Tab - Tab 2

Date Customer Name Customer ID Amount Direction

1/10/2018 Customer 1 1234567 1250 Incoming

1/11/2018 Customer 2 7654321 1000 Incoming

Un-reconciled Outgoing Tab - Tab 3

Same columns as first two tabs, and no data if reconciled and data if any transactions don't match.

Un-reconciled Incoming Tab - Tab 4

Same columns as first two tabs, no data if reconciled and data if any transactions don't match.

Yes, if reconciled, that will mean the sums match, if non-reconciled, that would mean the sums do not match.

Thanks.