DATA Step, Macro, Functions and more

Selecting first record based on two groups?

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Selecting first record based on two groups?

I have been trying to use the first/last commands for the last hour and trying to figure out how to get this to work.

I have the following sample lab data:

id date count year
1 12/5/2007 < 75 2007
1 2/27/2008 500 2008
1 6/30/2008 < 75 2008
1 10/27/2008 < 75 2008
1 2/23/2009 900 2009
1 6/1/2009 < 75 2009
1 8/24/2009 < 75 2009
1 12/7/2009 < 75 2009
2 4/25/2007 77 2007
2 7/6/2007 < 75 2007
2 8/27/2007 < 75 2007
2 11/26/2007 < 75 2007
2 1/28/2008 < 75 2008
2 6/30/2008 < 75 2008
2 11/5/2008 < 75 2008
2 2/9/2009 < 75 2009
2 5/11/2009 < 75 2009
2 9/23/2009 < 75 2009
2 3/1/2010 < 75 2010

So as you noticed, each person may have multiple labs per year. I want to grab the first lab (by date) for each year for each person. So basically I want a table that looks like this:

id date count year
1 12/5/2007 < 75 2007
1 2/27/2008 500 2008
1 2/23/2009 900 2009
2 4/25/2007 77 2007
2 1/28/2008 < 75 2008
2 2/9/2009 < 75 2009
2 3/1/2010 < 75 2010

So far, I have tried sorting it by ID and date created a dataset using first.date; but that didn't work.

I was wondering if someone can provide me with some help? I am pretty lost on how to do this!

Thanks!


Accepted Solutions
Solution
‎04-12-2013 08:12 PM
Respected Advisor
Posts: 4,937

Re: Selecting first record based on two groups?

It's FIRST.YEAR that must be used, you want the first record (date) within each year :

proc sort data=sample; by id year date; run;

data want;
set sample; by id year;
if first.year;
run;

PG

PG

View solution in original post


All Replies
Solution
‎04-12-2013 08:12 PM
Respected Advisor
Posts: 4,937

Re: Selecting first record based on two groups?

It's FIRST.YEAR that must be used, you want the first record (date) within each year :

proc sort data=sample; by id year date; run;

data want;
set sample; by id year;
if first.year;
run;

PG

PG
🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 1430 views
  • 0 likes
  • 2 in conversation