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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.