topic How to create a counting variable for nested groups? in SAS Programming

How to create a counting variable for nested groups?

saurabhc — Tue, 12 Nov 2013 22:53:49 GMT

Hi,

I have data that look like this.

id date

1 1/1/2001

1 1/2/2002

1 1/2/2003

2 1/1/2005

2 1/2/2005

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/2/2002 2

1 1/2/2003 3

2 1/1/2005 1

2 1/2/2005 2

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!

Patrick — Tue, 12 Nov 2013 23:06:19 GMT

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;

saurabhc — Tue, 12 Nov 2013 23:14:24 GMT

Hi Patrick,

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