Data have;
Input account_id Credit_amount debit_amount;
Cards;
1 10 100
2 20 200
1 100 10
3 20 200
4 10 100
2 200 20
5 10 50
;
Run;
data temp;
set have;
n=_N_;
run;
proc sort data=temp;
by account_id;
run;
data temp;
set temp;
flag=0;
if Credit_amount=lag1(debit_amount) and debit_amount=lag1(Credit_amount) and account_id=lag1(account_id) then flag=1;
run;
proc sort data=temp out=want(drop=n);
by n;
run;
@yashpande wrote:
Hi all,
I have below data. I need to flag observations based on combination. Below is the sample data
Data have;
Input account_id 8. Credit_amount 8. debit_amount 8.;
Cards;
1 10 100
2 20 200
1 100 10
3 20 200
4 10 100
2 200 20
5 10 50
;
Run;
So if you look at observation 1 and 3
Debit and credit are interchanged and hence this transaction needs to be flagged.
What is the actual rule involved? How does the data tell us that the values were interchanged? Can your data never have the credit amount greater than debit amount?
Suppose you have a slightly different set where you have the first record repeat. What do you want?
Data have;
Input account_id 8. Credit_amount 8. debit_amount 8.;
Cards;
1 10 100
2 20 200
1 100 10
3 20 200
4 10 100
2 200 20
5 10 50
1 10 100
;
Run;
I have a suspicion there might be date or time component to this problem that has not been mentioned.
Data have;
Input account_id Credit_amount debit_amount ;
Cards;
1 10 100
2 20 200
1 100 10
3 20 200
4 10 100
2 200 20
5 10 50
;
Run;
data _null_;
if 0 then set have;
length flag $8;
if _n_=1 then do;
dcl hash H (multidata:'y') ;
h.definekey ('account_id','Credit_amount','debit_amount') ;
h.definedata ('account_id','Credit_amount','debit_amount','flag') ;
h.definedone () ;
end;
set have end=last;
flag='N';
if h.check(key:account_id,key:debit_amount,key:Credit_amount)=0 then do;
flag='Y';
h.replace(key:account_id, key:debit_amount, key:Credit_amount,data:account_id, data:debit_amount, data:Credit_amount ,data:flag);
end;
h.add();
if last then h.output(dataset:'want');
run;
An easy way:
data test;
set have;
smaller = min(credit_amount, debit_amount);
larger = max(credit_amount, debit_amount);
run;
proc sort data=test;
by account_id smaller larger;
run;
data want;
set test;
by account_id smaller larger;
if larger=smaller then flag='N';
else if first.larger=0 or last.larger=0 then flag='Y';
else flag='N';
drop smaller larger;
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.