DATA Step, Macro, Functions and more

How to create a counting variable for nested groups?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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
Respected Advisor
Posts: 4,173

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;

View solution in original post


All Replies
Solution
‎11-12-2013 06:06 PM
Respected Advisor
Posts: 4,173

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.

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

Discussion stats
  • 2 replies
  • 4619 views
  • 1 like
  • 2 in conversation