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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.