topic running count by group in SAS Programming

running count by group

gzr2mz39 — Tue, 28 Jun 2011 05:48:40 GMT

I sorted by id and date (mm/dd/yy).

I need to create a running count by id and job (while keeping the dates in order).

How do I create the variable count?

id date job count

1 1/1/10 x 1

1 1/15/10 y 2

1 2/2/10 y 2

1 4/1/10 x 3

1 5/1/10 x 3

1 4/2/11 z 4

2 4/3/09 x 1

2 4/6/09 m 2

2 5/8/09 m 2

2 6/8/10 m 2

2 7/8/10 y 3

2 7/8/11 m 4

2 8/8/11 m 4

running count by group

Ksharp — Tue, 28 Jun 2011 06:47:36 GMT

id date job count

1 1/1/10 x 1

1 1/15/10 y 2

1 2/2/10 y 2

1 4/1/10 x 3

1 5/1/10 x 3

1 4/2/11 z 1

2 4/3/09 x 1

2 4/6/09 m 2

2 5/8/09 m 2

2 6/8/10 m 2

2 7/8/10 y 3

2 7/8/11 m 4

2 8/8/11 m 4

Why this obs 's count is 1 not 4? There are some other logic?

Ksharp

Re: running count by group

Ksharp — Tue, 28 Jun 2011 06:56:03 GMT

If that obs were 4,then it is easy.

data temp;
input id date : mmddyy10. job  $;
format date mmddyy10.;
cards;
1 1/1/10 x 
1 1/15/10 y 
1 2/2/10 y 
1 4/1/10 x 
1 5/1/10 x 
1 4/2/11 z 
2 4/3/09 x 
2 4/6/09 m 
2 5/8/09 m 
2 6/8/10 m 
2 7/8/10 y 
2 7/8/11 m 
;
run; 
data want;
 set temp;
 if id ne lag(id) then count=0;
 if job ne lag(job) then count+1;
run;

Ksharp

Message was edited by: xia keshan

Re: running count by group

MikeZdeb — Wed, 29 Jun 2011 16:49:21 GMT

Hi ... try some FIRST. variables ...

* leave C1 in the data set to see if logic worked;

data test;

input id date : mmddyy. job : $1. c1;

format date mmddyy10.;

datalines;

1 1/1/10 x 1

1 1/15/10 y 2

1 2/2/10 y 2

1 4/1/10 x 3

1 5/1/10 x 3

1 4/2/11 z 4

2 4/3/09 x 1

2 4/6/09 m 2

2 5/8/09 m 2

2 6/8/10 m 2

2 7/8/10 y 3

2 7/8/11 m 4

2 8/8/11 m 4

;

run;

* create C2 (should be same as C1);

data test;

set test;

by id job notsorted;

c2+(-c2*first.id);

c2+first.job;

run;

* hey, it is !!! ;

proc print data=test;

run;

id date job c1 c2

1 01/01/2010 x 1 1

1 01/15/2010 y 2 2

1 02/02/2010 y 2 2

1 04/01/2010 x 3 3

1 05/01/2010 x 3 3

1 04/02/2011 z 4 4

2 04/03/2009 x 1 1

2 04/06/2009 m 2 2

2 05/08/2009 m 2 2

2 06/08/2010 m 2 2

2 07/08/2010 y 3 3

2 07/08/2011 m 4 4

2 08/08/2011 m 4 4