BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nick9123
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
PeterClemmensen
Tourmaline | Level 20
proc sort data=have out=want nodupkey;
   by account payment;
run;
novinosrin
Tourmaline | Level 20

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 413 views
  • 0 likes
  • 4 in conversation