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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.