topic How to find which numbers from a setadd up to a particular amount in SAS Data Management

How to find which numbers from a setadd up to a particular amount

SASonist — Wed, 31 Jan 2018 04:31:57 GMT

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.

Re: How to find which numbers from a setadd up to a particular amount

kiranv_ — Wed, 31 Jan 2018 06:16:14 GMT

it is little bit confusing. can you please illustrate with simple example with input you have and output you want to see.

Re: How to find which numbers from a setadd up to a particular amount

Astounding — Wed, 31 Jan 2018 07:19:22 GMT

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;

That's at least the framework for the programming. It's not easy, even for experienced programmers.

Re: How to find which numbers from a setadd up to a particular amount

ballardw — Wed, 31 Jan 2018 15:44:53 GMT

Is there any other data involved such as a transaction date, transaction type or similar?