SAS Programming

DATA Step, Macro, Functions and more
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;

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1006 views
  • 1 like
  • 4 in conversation