topic Re: SUM BASED ON ROW BY ROW in SAS Programming

SUM BASED ON ROW BY ROW

rohithverma — Wed, 01 Jul 2020 11:45:23 GMT

I have a data like below

laonno debit childno credit

1234 4162.98 . .

1234 0.02 . .

. . 1234 1387.66

I need output as when the debit sum is equals to credit sum then for those observations flag should be generated as mentioned below

laonno debit childno credit flag

1234 4162.98 . . matched

1234 0.02 . . N

. . 1234 1387.66 matched

The data rows will be dynamic but when the sum of debit matches credit then the following flag should be as "MATCHED" .

Please help me . Thanks in advance

Re: SUM BASED ON ROW BY ROW

PaigeMiller — Wed, 01 Jul 2020 11:56:30 GMT

Please provide the data via working SAS data step code.

Instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Re: SUM BASED ON ROW BY ROW

rohithverma — Wed, 01 Jul 2020 12:06:02 GMT

data yy;
infile datalines;
input laonno: $ debit: childno:$ credit:;
datalines;

1234 4162.98 . .

1234 0.02 . .

. . 1234 1387.66

. . 1234 1387.66
run;

Re: SUM BASED ON ROW BY ROW

PaigeMiller — Wed, 01 Jul 2020 14:18:38 GMT

Now, I'd like a better explanation of the logic.

It seems as if you are saying that if a debit matches the sum of any combination of credits, then you list this as a match. Is that correct? Do the credits have to be consecutive? Does order matter at all? Can the credits be any number of records that adds to a debit?

You would help yourself immensely by providing an example that has several debits, some that have matches and some that do not.

Re: SUM BASED ON ROW BY ROW

PeterClemmensen — Wed, 01 Jul 2020 14:52:03 GMT

If you data is representative, here is one way

data yy;
infile datalines;
input laonno: $ debit: childno:$ credit:;
datalines;
1234 4162.98 . .
1234 0.02 . .
. . 1234 1387.66
. . 1234 1387.66
. . 1234 1387.66
run;

data want (drop=s);
    if _N_ = 1 then do;
        dcl hash h ();
        h.definekey ('childno');
        h.definedata ('s');
        h.definedone ();

        dcl hash hh ();
        hh.definekey ('laonno');
        hh.definedone ();
        
        do until (lr);
            set yy(where=(childno)) end=lr;
            if h.find() ne 0 then s = credit;
            else                  s = sum(s, credit);
            h.replace();
        end;

    end;

    set yy;
    s = .;

    if h.find(key : laonno) = 0 & round(s, .001) = debit then do;
        flag = 'Matched';
        hh.ref();
    end;
    else flag = 'N';

    if hh.check(key : childno) = 0 then flag = 'Matched';

run;

Result :

laonno debit   childno credit    flag 
1234   4162.98 .       .         Matched 
1234   0.02    .       .         N 
.      .       1234    1387.66   Matched 
.      .       1234    1387.66   Matched 
.      .       1234    1387.66   Matched

Re: SUM BASED ON ROW BY ROW

rohithverma — Wed, 01 Jul 2020 15:10:07 GMT

if a debit matches the sum of any combination of credits, then you list this as a match - yes perfect
Do the credits have to be consecutive - NO .It may be in any order.
Does order matter at all-No order doesn't matter
Can the credits be any number of records that adds to a debit- yes we cant define the particular number.

Please find the updated dataset as mentioned below :

data yy;
infile datalines ;
input laonno: $ debit: childno:$ credit:;
datalines;

1234 4162.98 . .

1234 0.02 . .

. . 1234 1387.66

. . 1234 1387.66

. . 1234 1387.66
4567 100 . .
4567 101 . .
. . 4567 46
. . 4567 67
. . 4567 54
7890 46 . .
7890 67 . .
7890 54 . .
. . 7890 100

. . 7890 101
;

run;

These are the possible ways which i am working on

output::
data yy;
infile datalines ;
input laonno: $ debit: childno:$ credit: flag:$;
datalines;

1234 4162.98 . . y

1234 0.02 . . n

. . 1234 1387.66 y

. . 1234 1387.66 y

. . 1234 1387.66 y
4567 100 . . y
4567 101 . . n
. . 4567 46 y
. . 4567 67 n
. . 4567 54 y
7890 46 . . y
7890 67 . . n
7890 54 . . y
. . 7890 100 y

. . 7890 101 n
;

run;

Re: SUM BASED ON ROW BY ROW

rohithverma — Wed, 01 Jul 2020 15:11:35 GMT

Thanks for posting but i need in sas please

Re: SUM BASED ON ROW BY ROW

PeterClemmensen — Wed, 01 Jul 2020 15:15:04 GMT

I don't understand? This is SAS?

Re: SUM BASED ON ROW BY ROW

rohithverma — Wed, 01 Jul 2020 15:31:38 GMT

but the following flag is generating with irrespective of the logic to other loanno's as well

Re: SUM BASED ON ROW BY ROW

PaigeMiller — Wed, 01 Jul 2020 15:35:17 GMT

Yes, the solution by @PeterClemmensen is a portion of SAS that uses Hash objects to perform tasks that would be difficult or impossible with other DATA step commands. As far as I can see, it would be extremely difficult or impossible to do this in a DATA step without using Hash objects.

Re: SUM BASED ON ROW BY ROW

rohithverma — Wed, 01 Jul 2020 20:38:38 GMT

Could you please apply the same logic to the blow input please as i tried but i am fetching flag as wrong. So , thanks in advance

data yy;
infile datalines;
input laonno: $ debit: childno:$ credit:;
datalines;
1234 4162.98 . .
1234 0.02 . .
. . 1234 1387.66
. . 1234 1387.66
. . 1234 1387.66
4567 5439 . .
4567 1256 . .
. . 4567 1864.66
. . 4567 1864.66

run;

Re: SUM BASED ON ROW BY ROW

PeterClemmensen — Fri, 03 Jul 2020 13:45:58 GMT

How many observations do you have for your largest group of laono/childno? In this sample data your largest group for laono has 3 debit observations.

You want to generate all possible sums of the debit/credit amount. This is possible for small data and small groups. The three numbers 46, 67 and 54 can generate 7 (2^3-1) possible sums (46, 67, 54, 113, 121, 100 and 167). The number of possible sums grows fast for more numbers. 20 numbers can generate more than 1Million sums:

data _null_;
    x = 20;
    y = 2**x - 1;
    put y=;
run;

Re: SUM BASED ON ROW BY ROW

PeterClemmensen — Fri, 03 Jul 2020 14:03:21 GMT

Here is an approach that gives you your desired result for relatively small groups. Set the array sizes for a and t plus the Binary10. format properly

data yy;
infile datalines ;
input laonno: $ debit: childno:$ credit:;
datalines;
1234 4162.98 .    .       
1234 0.02    .    .       
.    .       1234 1387.66 
.    .       1234 1387.66 
.    .       1234 1387.66 
4567 100     .    .       
4567 101     .    .       
.    .       4567 46      
.    .       4567 67      
.    .       4567 54      
7890 46      .    .       
7890 67      .    .       
7890 54      .    .       
.    .       7890 100     
.    .       7890 101     
;

data want (keep= laonno debit childno credit flag);

   if _N_ = 1 then do;

      array a {10} _temporary_ (10*0);
      array t (k) t1-t10;

      dcl hash deb(multidata : "Y");
      deb.definekey("laonno");
      deb.definedata("sumdeb");
      do over t;
         deb.definedata(vname(t));
      end;
      deb.definedone();

      dcl hash cre(multidata : "Y");
      cre.definekey("childno");
      cre.definedata("sumcre");
      do over t;
         cre.definedata(vname(t));
      end;
      cre.definedone();

      do until (lr);
         set yy end=lr;
         by laonno notsorted;

         if first.laonno then do;
            i=0;
            call stdize('replace', 'mult=', 0, of a[*], _N_);
         end;

         i + 1;
         if laonno then a[i] = debit;
         else           a[i] = credit;

         if last.laonno then do;
            do j = 1 to 2**dim(a) - 1;
               s = put(j, binary10.);
               sum = 0;
               do k = 1 to dim(a);
                  t   = input(char(s,k), 8.) * a[k];
                  sum = round(sum + char(s,k) * a[k], .001);
               end;
               if laonno then do;
                  sumdeb = sum; deb.add();
               end;
               if childno then do;
                  sumcre = sum; cre.add();
               end;
            end;
         end;
      end;
   end;

   set yy;
   flag = "N";
  
   if laonno then do;
      do while (deb.do_over()=0);
         if debit in t then do while (cre.do_over(key : laonno)=0);
            if sumdeb = sumcre then flag = "Y";
         end;
      end;
   end;

   if childno then do;
      do while (cre.do_over()=0);
         if credit in t then do while (deb.do_over(key : childno)=0);
            if sumcre = sumdeb then flag = "Y";
         end;
      end;
   end;

run;

Result:

laonno debit   childno credit   flag 
1234   4162.98 1234    .        Y 
1234   0.02    1234    .        N 
1234   .       1234    1387.66  Y 
1234   .       1234    1387.66  Y 
1234   .       1234    1387.66  Y 
4567   100.00  4567    .        Y 
4567   101.00  4567    .        N 
4567   .       4567    46.00    Y 
4567   .       4567    67.00    N 
4567   .       4567    54.00    Y 
7890   46.00   7890    .        Y 
7890   67.00   7890    .        N 
7890   54.00   7890    .        Y 
7890   .       7890    100.00   Y 
7890   .       7890    101.00   N