I have a data like below
laonno debit childno credit
1234 4162.98 . .
1234 0.02 . .
. . 1234 1387.66
. . 1234 1387.66
. . 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
. . 1234 1387.66 matched
. . 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
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/
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;
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.
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;
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
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
I don't understand? This is SAS?
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.