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
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.
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.
@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.
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.
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: