Count column to a max value then restart count

[1SasUser1]

Hi,

SAS Enterprise Guide 5.1 

I have multiple people with a level of either 1 or 0. I want to count this column up to 3 and then restart the count again.

If the Level = 1 then Recount = 1 unless the count is at 3, then it would go back to zero.

Please see below what I want, where the recount starts again per person's number:

Number	Level	Recount
12300000	1	1
12300000	1	2
12300000	1	3
12300000	1	0
12300000	1	1
12300000	1	2
12300000	1	3
12300000	1	0
12300000	1	1
12300000	1	2
12300000	1	3
12300000	1	0
45600000	1	1
45600000	0	0
45600000	1	1
45600000	1	2
45600000	0	0
45600000	1	1
45600000	0	0
45600000	1	1
45600000	1	2
45600000	1	3
45600000	1	0
45600000	0	0
78900000	0	0
78900000	0	0
78900000	1	1
78900000	0	0
78900000	0	0
78900000	1	1
78900000	1	2
78900000	0	0
78900000	0	0
78900000	1	1
78900000	1	2
78900000	1	3
74100000	0	0
74100000	0	0
74100000	0	0
74100000	1	1
74100000	0	0
74100000	1	1
74100000	0	0
74100000	0	0
74100000	1	1
74100000	1	2
74100000	0	0
74100000	0	0
85200000	0	0
85200000	0	0
85200000	0	0
85200000	0	0
85200000	1	1
85200000	0	0
85200000	0	0
85200000	0	0
85200000	0	0
85200000	0	0
85200000	1	1
85200000	0	0

Data:

data WORK.count;
infile datalines dsd truncover;
input Number:BEST12. Level:BEST12.;
format Number BEST12. Level BEST12.;
datalines;
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
45600000 1
45600000 0
45600000 1
45600000 1
45600000 0
45600000 1
45600000 0
45600000 1
45600000 1
45600000 1
45600000 1
45600000 0
78900000 0
78900000 0
78900000 1
78900000 0
78900000 0
78900000 1
78900000 1
78900000 0
78900000 0
78900000 1
78900000 1
78900000 1
74100000 0
74100000 0
74100000 0
74100000 1
74100000 0
74100000 1
74100000 0
74100000 0
74100000 1
74100000 1
74100000 0
74100000 0
85200000 0
85200000 0
85200000 0
85200000 0
85200000 1
85200000 0
85200000 0
85200000 0
85200000 0
85200000 0
85200000 1
85200000 0
;;;;

I've been using the below code so far, but it doesn't restart:

data work.count;
 set work.TEST;
  by Number;
       retain count;
       if first.Number then do;
		if Level = 1 then count=1; else  count=0;
		end;
		else do;
		if Level = 0 then count = 0; else count + 0;
	
	if Level= 1 then count + 1; else count + 0;
	end;
  
run;

Any help would be great!

Thanks in advance.

[Kurt_Bremser · Accepted solution]

Try this:

data count;
input Number Level;
datalines;
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
12300000 1
45600000 1
45600000 0
45600000 1
45600000 1
45600000 0
45600000 1
45600000 0
45600000 1
45600000 1
45600000 1
45600000 1
45600000 0
78900000 0
78900000 0
78900000 1
78900000 0
78900000 0
78900000 1
78900000 1
78900000 0
78900000 0
78900000 1
78900000 1
78900000 1
74100000 0
74100000 0
74100000 0
74100000 1
74100000 0
74100000 1
74100000 0
74100000 0
74100000 1
74100000 1
74100000 0
74100000 0
85200000 0
85200000 0
85200000 0
85200000 0
85200000 1
85200000 0
85200000 0
85200000 0
85200000 0
85200000 0
85200000 1
85200000 0
;

data want;
set count;
by number notsorted;
retain recount;
if first.number then recount = 0;
if level = 0
then recount = 0;
else recount = mod(recount + 1,4);
run;

Note that I simplified and fixed your example data step.

[1SasUser1]

Excellent. Works perfectly! Thank You.

[Kurt_Bremser]

Fine! Is it clear to you what I am doing in detail, or do you need some further explanation?

[1SasUser1]

I would actually appreciate if you could explain it in detail. Whilst it does exactly what I want, I am unsure about what 'mod' is doing and what it is meant by 'notsorted'. I am also unsure why the first value isn't 0 due to this line: 

if first.number then recount = 0;

Thanks.

[Kurt_Bremser]

The mod() function does a modulo calculation. mod(x,4) means that only the values 0 to 3 can result, 4 turns to 0, 5 to 1, and so on.

While the statement you posted does set recount to zero, the next statement in the code will add 1 and apply the mod() function if level =1 in the first observation of a by group.

And the notsorted option in the by statement takes care of the fact that you have a sequence (78900000 - 74100000) where the following number is smaller than the previous one. Without that option, a strictly ascending sequence is required.

[1SasUser1]

Thank you. That makes sense now.

I have another situation if you would be able to help?

Currently the count resets at 3, but I have another two columns and I want a new count alongside to compare to the count you did.

If the count gets to 3 but the column 'Level' = 1 and pd = Arr (not greater than), then I want the count to be set to 1 and recount, otherwise the count you did before is correct (going back to zero).

I would like it like this: 

Number	Level	Recount	Pd	Arr	NewCount
123	1	1	-251.02	0	1
123	1	2	-235.1	235.1	2
123	1	3	-241.23	241.23	3
123	1	0	-237.72	237.72	1
123	0	0	-465.77	232.61	0
123	1	1	.	0	1
123	1	2	-215.89	215.89	2
123	1	3	-244.81	244.81	3
123	1	0	-241.41	241.41	1
123	1	1	-235.61	235.61	2
123	1	2	-233.42	233.42	3
123	1	3	-241.68	241.68	1

data NewCount;
input Number Level Recount Pd Arr;
datalines;
123 1 1 -251.02 0
123 1 2 -235.1 235.1
123 1 3 -241.23 241.23
123 1 0 -237.72 237.72
123 0 0 -465.77 232.61
123 1 1 . 0
123 1 2 -215.89 215.89
123 1 3 -244.81 244.81
123 1 0 -241.41 241.41
123 1 1 -235.61 235.61
123 1 2 -233.42 233.42
123 1 3 -241.68 241.68
;

Hope this makes sense.

Thanks again.

[Kurt_Bremser]

You will have to expand the if/then/else a little bit, from

if level = 0
then recount = 0;
else recount = mod(recount + 1,4);

to

if level = 0
then recount = 0;
else do;
  recount = mod(recount + 1,4);
  if recount = 0 and /* add your additional condition here */ then recount = 1;
end;

[1SasUser1]

Thank you again. It worked just how I wanted. Maybe a silly question, but how does it know to carry on the mod rule when I add the extra condition and not just put 1 and back to 0?

[Kurt_Bremser]

The mod() function does not care what it did in the previous iteration of the data step, it just works with the values fed to it at the current moment. And that is the value stored in the retained variable.

[1SasUser1]

ah okay, makes sense! Thanks again for all your help.