SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
New User
Posts: 1

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

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.

PROC Star
Posts: 503

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

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

Super User
Posts: 6,642

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

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.

Super User
Posts: 13,358

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 124 views
  • 0 likes
  • 4 in conversation