DATA Step, Macro, Functions and more

enumeration variable by groups

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

enumeration variable by groups

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


Accepted Solutions
Solution
‎05-01-2015 05:09 PM
Trusted Advisor
Posts: 1,204

Re: enumeration variable by groups

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;

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: enumeration variable by groups

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;

Contributor
Posts: 66

Re: enumeration variable by groups

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                   

Valued Guide
Posts: 858

Re: enumeration variable by groups

why

2 2/2/2011 1

?

Contributor
Posts: 66

Re: enumeration variable by groups

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.

Contributor
Posts: 66

Re: enumeration variable by groups

I ran your program above, but did not get the result I want. Thanks anyway.

Solution
‎05-01-2015 05:09 PM
Trusted Advisor
Posts: 1,204

Re: enumeration variable by groups

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;

Super User
Posts: 9,676

Re: enumeration variable by groups

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

N/A
Posts: 1

Re: enumeration variable by groups

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 1004 views
  • 0 likes
  • 5 in conversation