Hello Y'all,
I need some help selecting certain observations from a dataset to create a new table. For every member (ID), I want the:
Here's how the original dataset looks like; the rows in red mark the observations I want to have in my new dataset:
ID | INPBEG | INPPAID |
100 | 11/23/2008 | 560 |
100 | 11/23/2008 | 8000 |
100 | 12/5/2010 | 2100 |
101 | 3/9/2009 | 1000 |
101 | 3/20/2009 | 5000 |
101 | 3/30/2009 | 16000 |
101 | 3/30/2009 | 19000 |
101 | 3/30/2009 | 2000 |
102 | 11/6/2009 | 9000 |
102 | 11/6/2009 | 5000 |
103 | 11/5/2008 | 2050 |
103 | 11/5/2008 | 6700 |
104 | 5/17/2010 | 1400 |
104 | 7/9/2010 | 800 |
104 | 7/9/2010 | 4900 |
105 | 6/17/2009 | 3000 |
105 | 6/17/2009 | 750 |
105 | 11/20/2010 | 8500 |
105 | 12/30/2011 | 3600 |
105 | 12/30/2011 | 5000 |
As you can see from the red rows, there is no order/pattern of when a member will have duplicate dates. Right now my data is sorted by ID and INPBEG; that's how I know the bottom (last) observation of duplicate dates is the one I want.
I am using SAS 9.4
Thanks!
If the dataset is already ordered as you desire, just do
data want;
set have;
by id inpbeg;
if last.inpbeg;
run;
If the dataset is already ordered as you desire, just do
data want;
set have;
by id inpbeg;
if last.inpbeg;
run;
Something like this
data have;
informat INPBEG mmddyy10.;
input ID $ INPBEG INPPAID;
format INPBEG mmddyy10.;
datalines;
100 11/23/2008 560
100 11/23/2008 8000
100 12/05/2010 2100
101 03/09/2009 1000
101 03/20/2009 5000
101 03/30/2009 16000
101 03/30/2009 19000
101 03/30/2009 2000
102 11/06/2009 9000
102 11/06/2009 5000
103 11/05/2008 2050
103 11/05/2008 6700
104 05/17/2010 1400
104 07/09/2010 800
104 07/09/2010 4900
105 06/17/2009 3000
105 06/17/2009 750
105 11/20/2010 8500
105 12/30/2011 3600
105 12/30/2011 5000
;
proc sort data = have;
by id inpbeg;
run;
data want;
set have;
by ID INPBEG;
if last.INPBEG;
run;
Thanks for the step-by-step follow-up!!
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.