DATA Step, Macro, Functions and more

Selecting Certain Observations for New Dataset

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Selecting Certain Observations for New Dataset

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!


Accepted Solutions
Solution
‎11-08-2016 02:33 PM
Super User
Posts: 6,946

Re: Selecting Certain Observations for New Dataset

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

data want;
set have;
by id inpbeg;
if last.inpbeg;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎11-08-2016 02:33 PM
Super User
Posts: 6,946

Re: Selecting Certain Observations for New Dataset

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

data want;
set have;
by id inpbeg;
if last.inpbeg;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 552

Re: Selecting Certain Observations for New Dataset

[ Edited ]

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;
Contributor
Posts: 45

Re: Selecting Certain Observations for New Dataset

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

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 194 views
  • 1 like
  • 3 in conversation