DATA Step, Macro, Functions and more

Remove duplicate in dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Remove duplicate in dataset

I need some help to delete few rows from the below table based on some conditions.

 

For some IDs  for a Year the table has two entries one with value in the amt field and other is empty.

I have to delete the records of the IDs which has more than 1 entry for a year and the amt field is empty.

Please help.

 

IDYearAmt 
700000312009. 
700000312010. 
700000312011. Needs to Be Deleted
700000312011526.2906 
700000312012. 
700000312013200 
700000312014. 
700000312015. 
700000312016. Needs to Be Deleted
700000312016119 
700008952009. 
700008952010100 
700008952011. 
700008952012. Needs to Be Deleted
7000089520122186.864 
700008952013. 
700008952014. 
700008952015. Needs to Be Deleted
700008952015340.2716 
700008952016. 
7000089520176789 

Accepted Solutions
Solution
‎05-24-2017 03:24 PM
PROC Star
Posts: 7,492

Re: Remove duplicate in dataset

Posted in reply to Melvin_Sas

If you don't have multiple records for a given id and year that contain non-missing amounts, then the update statement would be your easiest solution. e.g.:

data want;
  update have (obs=0) have;
   by id year;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Solution
‎05-24-2017 03:24 PM
PROC Star
Posts: 7,492

Re: Remove duplicate in dataset

Posted in reply to Melvin_Sas

If you don't have multiple records for a given id and year that contain non-missing amounts, then the update statement would be your easiest solution. e.g.:

data want;
  update have (obs=0) have;
   by id year;
run;

Art, CEO, AnalystFinder.com

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 127 views
  • 1 like
  • 2 in conversation