Hi there,
I’ve working on comparing transactions from two different sources that are supposed to share the same data as one is updated from the other but it seems that the one has been updated with subtotal amounts and at different times. I’m looking for a way to find the numbers from a set sharing the same ID that equal a particular amount. For instance, let’s say I have a set of numbers from Variable X: 1,2,5,10,5,64,32,2 each in a different row all linked to a specific ID in variable Y and I want to know which sum from that set equals 34 which is indicated in each instance of varable z under that same ID. Is there a built in function to do this? I have tried running a cumulative sum upward and downward by indexing each instance of variable X under the same ID in variable Y but it never matches, however when I take a closer look I find that two or more amounts match the amount under that ID in variable Z. I hope you can help me figure this out.
I ‘m pretty new at SAS so excuse my ignorance. I thought maybe a while loop would do it but I would need to do it in order, which I sort of already tried.
Thanks in advance.
it is little bit confusing. can you please illustrate with simple example with input you have and output you want to see.
It's hard. It can be done.
Here's an approach, but not the full code that would get you there.
Construct a temporary array holding all X for a single ID:
array xvals {30} _temporary_;
Fill it so that xvals{1} = 1, xvals{2}=2, ... , xvals{7} = 32, xvals{8} = 2.
As the number of elements increases, the processing takes quite a while That's the reason I cut things off at 30. Even that is probably more than you want to consider ... there would be 2**30 combinations to consider.
Find the sum of each combination. I'm going to hard-code the equation, without adding the complication of counting how many of the elements are actually filled. That's just to show you the nature of the approach. The programming does become complex at that point.
do c1=0, 1;
do c2=0, 1;
do c3=0, 1;
...
do c8=0, 1;
total = c1 * xvals{1} + c2 * xvals{2} + ... + c8 * xval{8};
if total=34 then do;
** Figuring out what to do when a match is found is not that easy either! More than 1 combination can total to 34 ;
end;
end;end;end;end;
end;end;end;end;
That's at least the framework for the programming. It's not easy, even for experienced programmers.
Is there any other data involved such as a transaction date, transaction type or similar?
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.