- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
In the example below, i want to check if an account number has a duplicate, if it does then check if the payment amount is the same. If the payment amount is the same, then delete the row from the table.
DATE Account # Payment
08312019 111 100
09302019 111 50
08312019 222 150
09302019 222 150
08312019 333 150
09302019 333 200
08312019 444 300
09302019 444 150
08312019 555 75
09302019 555 75
08312019 666 20
09302019 666 20
08312019 777 300
09302019 777 100
08312019 888 50
09302019 888 75
08312019 999 200
09302019 999 150
08312019 000 75
09302019 000 75
If it helps, the columns are in character format but can be reformatted if needed.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Nick9123
data have;
input DATE :mmddyy10. Account Payment;
format date mmddyy10.;
cards;
08312019 111 100
09302019 111 50
08312019 222 150
09302019 222 150
08312019 333 150
09302019 333 200
08312019 444 300
09302019 444 150
08312019 555 75
09302019 555 75
08312019 666 20
09302019 666 20
08312019 777 300
09302019 777 100
08312019 888 50
09302019 888 75
08312019 999 200
09302019 999 150
08312019 000 75
09302019 000 75
;
proc sql;
create table want as
select *
from have
group by account
having not (count(account)>1 and std(payment)=0)
order by account,date;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please try the below code
data have;
input DATE Account Payment;
cards;
08312019 111 100
09302019 111 50
08312019 222 150
09302019 222 150
08312019 333 150
09302019 333 200
08312019 444 300
09302019 444 150
08312019 555 75
09302019 555 75
08312019 666 20
09302019 666 20
08312019 777 300
09302019 777 100
08312019 888 50
09302019 888 75
08312019 999 200
09302019 999 150
08312019 000 75
09302019 000 75
;
proc sort data=have;
by account payment date;
run;
data want;
set have;
by account payment date;
if last.payment;
run;
Jag
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sort data=have out=want nodupkey;
by account payment;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Nick9123
data have;
input DATE :mmddyy10. Account Payment;
format date mmddyy10.;
cards;
08312019 111 100
09302019 111 50
08312019 222 150
09302019 222 150
08312019 333 150
09302019 333 200
08312019 444 300
09302019 444 150
08312019 555 75
09302019 555 75
08312019 666 20
09302019 666 20
08312019 777 300
09302019 777 100
08312019 888 50
09302019 888 75
08312019 999 200
09302019 999 150
08312019 000 75
09302019 000 75
;
proc sql;
create table want as
select *
from have
group by account
having not (count(account)>1 and std(payment)=0)
order by account,date;
quit;