How to create a counting variable for nested groups?

Solved
Occasional Contributor
Posts: 19

How to create a counting variable for nested groups?

Hi,

I have data that look like this.

id date

1 1/1/2001

1 1/1/2001

1 1/2/2002

1 1/2/2002

1 1/2/2003

1 1/2/2003

2 1/1/2005

2 1/1/2005

2 1/1/2005

2 1/2/2005

2 1/2/2005

2 1/3/2006

2 1/3/2006

How do I count by id and then by date? like so

id date        count

1 1/1/2001     1

1 1/1/2001     1

1 1/2/2002     2

1 1/2/2002     2

1 1/2/2003     3

1 1/2/2003     3

2 1/1/2005     1

2 1/1/2005     1

2 1/1/2005     1

2 1/2/2005     2

2 1/2/2005     2

2 1/3/2006     3

2 1/3/2006     3

so it counts every new date within the id but starts over at 1 for every new id? I tried the following but to no avail.

data work.a;

by id;

if first.id then count=0;

if count=0 then do;

fdate=date;

count+1;

end;

edate=date;

diff=edate=fdate;

if diff>0 then count+1;

run;

Thanks!

Accepted Solutions
Solution
‎11-12-2013 06:06 PM
Posts: 4,736

Re: How to create a counting variable for nested groups?

Untested - but should work. Using "count+1" implicitly makes count a retained variable.

data work.a;

by id date;

if first.id then count=1;

else if first.date then count+1;

run;

All Replies
Solution
‎11-12-2013 06:06 PM
Posts: 4,736

Re: How to create a counting variable for nested groups?

Untested - but should work. Using "count+1" implicitly makes count a retained variable.

data work.a;

by id date;

if first.id then count=1;

else if first.date then count+1;

run;

Occasional Contributor
Posts: 19

Re: How to create a counting variable for nested groups?

Hi Patrick,

Thank you. It works. I can now use the properties of count to manipulate the observations.

🔒 This topic is solved and locked.