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!
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
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
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.