Reconcile Two Data Sets in SAS

[iMaq_23]

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

[Astounding]

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.

[iMaq_23]

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.

[ballardw]

---

@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-dat... 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.

[iMaq_23]

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/09/2018             12345670001                Customer 1                    250                   Outgoing

1/10/2018             12345670001                Customer 1                    750                   Incoming

1/10/2018             12345670001                Customer 1                    250                   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.

[ballardw]

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?

[iMaq_23]

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.