Hi,
I have a question about how to count within groups. I have a data set like below and I want to keep the records only on the first same date for each person. I am trying to create new variable [count] like the second one in below, so that I could keep only count=1 to get what I need. I tried different ways, but always got result like variable [count_wrong]. Could you give me some hints? Thank so much!!
id date
1 1/1/2011
1 1/1/2011
1 2/3/2011
2 2/2/2011
2 3/5/2012
2 3/5/2012
2 4/8/2012
3 2/5/2011
3 2/5/2011
3 3/8/2011
3 4/9/2011
id date count count_wrong
1 1/1/2011 1 1
1 1/1/2011 1 2
1 2/3/2011 2 1
2 2/2/2011 1 1
2 3/5/2012 2 1
2 3/5/2012 2 2
2 4/8/2012 3 1
3 2/5/2011 1 1
3 2/5/2011 1 2
3 3/8/2011 2 1
3 4/9/2011 3 1
data want(drop=count);
set have;
by id date;
if first.id then count=0;
if first.date then count+1;
if count=1;
run;
if a count is necessary give me an example of the output that you want, otherwise I think this is what you are looking for:
data have;
infile cards dsd;
informat date mmddyy10.;
format date mmddyy10.;
input id $ date;
cards;
1,1/1/2011
1,1/1/2011
1,2/3/2011
2,2/2/2011
2,3/5/2012
2,3/5/2012
2,4/8/2012
3,2/5/2011
3,2/5/2011
3,3/8/2011
3,4/9/2011
;
run;
proc sort data=have;by id date;
data want(drop=ldate) check;
set have;
by id;
ldate = lag(date);
output check;
if not first.id and ldate = date then output want;
run;
Thanks Mark. Here is the final data I want. Actually I do not need the variable [count]. I just wanted to find a way to get the data I need. Thanks.
id date count
1 1/1/2011 1
1 1/1/2011 1
2 2/2/2011 1
3 2/5/2011 1
3 2/5/2011 1
why
2 | 2/2/2011 | 1 |
?
Actually I do not need the variable [count]. I just want to keep all rows of record on the first date for each person. Thanks.
I ran your program above, but did not get the result I want. Thanks anyway.
data want(drop=count);
set have;
by id date;
if first.id then count=0;
if first.date then count+1;
if count=1;
run;
data have; infile cards dsd; informat date mmddyy10.; format date mmddyy10.; input id $ date; cards; 1,1/1/2011 1,1/1/2011 1,2/3/2011 2,2/2/2011 2,3/5/2012 2,3/5/2012 2,4/8/2012 3,2/5/2011 3,2/5/2011 3,3/8/2011 3,4/9/2011 ; run; data want; set have; by id date; if first.id then count=0; count+first.date; if count=1; run;
Xia Keshan
data have;
infile cards dsd;
informat date mmddyy10.;
format date mmddyy10.;
input id $ date;
cards;
1,1/1/2011
1,1/1/2011
1,2/3/2011
2,2/2/2011
2,3/5/2012
2,3/5/2012
2,4/8/2012
3,2/5/2011
3,2/5/2011
3,3/8/2011
3,4/9/2011
;
run;
data want;
set have;
by id date;
if first.id then count=0;
if first.date then count+1;
retain count;
run;
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.