topic Selecting Certain Observations for New Dataset in SAS Programming

Selecting Certain Observations for New Dataset

TXSASneophyte — Mon, 07 Nov 2016 14:35:07 GMT

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:

observations with unique dates
when there are duplicate dates per member, I want the bottom (last) observation

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!

Re: Selecting Certain Observations for New Dataset

Kurt_Bremser — Mon, 07 Nov 2016 14:40:49 GMT

If the dataset is already ordered as you desire, just do

data want;
set have;
by id inpbeg;
if last.inpbeg;
run;

Re: Selecting Certain Observations for New Dataset

PeterClemmensen — Mon, 07 Nov 2016 14:46:38 GMT

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;

Re: Selecting Certain Observations for New Dataset

TXSASneophyte — Tue, 08 Nov 2016 19:34:04 GMT

Thanks for the step-by-step follow-up!!