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.
ID | Year | Amt | |
70000031 | 2009 | . | |
70000031 | 2010 | . | |
70000031 | 2011 | . | Needs to Be Deleted |
70000031 | 2011 | 526.2906 | |
70000031 | 2012 | . | |
70000031 | 2013 | 200 | |
70000031 | 2014 | . | |
70000031 | 2015 | . | |
70000031 | 2016 | . | Needs to Be Deleted |
70000031 | 2016 | 119 | |
70000895 | 2009 | . | |
70000895 | 2010 | 100 | |
70000895 | 2011 | . | |
70000895 | 2012 | . | Needs to Be Deleted |
70000895 | 2012 | 2186.864 | |
70000895 | 2013 | . | |
70000895 | 2014 | . | |
70000895 | 2015 | . | Needs to Be Deleted |
70000895 | 2015 | 340.2716 | |
70000895 | 2016 | . | |
70000895 | 2017 | 6789 |
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
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.