Solved
New Contributor
Posts: 2

# conditional counter (forward and backward looking)

Dear community,

In my data I want to use a counter variable that counts under special conditions. The counter should start to count everytime primary is equal to 4 until it stops being 4. In the case primary = 5 prior to primary = 4 the counter shouldn't start. The same goes (backwards looking)  for a primary=5 which appears after a sequence of primary=4 (the counter should be set to zero for the whole sequence before). Think of it as the employment status where the counter counts the time in unemployment (primary=4). While this duration is just interesting if the individual was in the labor force before and stays in after, I don't want the duration to be count if primary = 5 (appears when the individual is not in the labor force). Here is an example(normally there are 15000 individuals and time is  up to 1500):

`data work.sample;input id \$ primary \$ time (\$);datalines;1 5 1`

1  5  2

1  4  3

1  4  4

1  4  5

1  1  6

2  1  1

2  1  2

2  4  3

2  4  4

2  4  5

2  5  6

3  1  1

3  4  2

3  4  3

3  4  4

3  1  5

3  1  6`;`

I am able to set up the counter but so far I have no clue how to bring in my conditions on primary = 5 and the following and earlier periods.

data test1;

set test1;

if primary = 4 and lag(primary) ^= 4 then duration = 1;

if primary = 4 and lag(primary) = 4 then duration+1;

if primary ^= 4 then duration = 0;

run;

The output in the end should look like this:

input id \$ primary \$ time \$ duration (\$)

`1 5 1 0`

1  5  2  0

1  4  3  0 <- no count after because of the primary=5

1  4  4  0

1  4  5  0

1  1  6  0

2  1  1  0

2  1  2  0

2  4  3  0

2  4  4  0

2  4  5  0

2  5  6  0  <- no count before because of the primary=5

3  1  1  0

3  4  2  1

3  4  3  2

3  4  4  3

3  1  5  0

3  1  6  0

I would be really thankful for any help!

Accepted Solutions
Solution
‎03-22-2015 09:57 PM
Posts: 3,167

## Re: conditional counter (forward and backward looking)

Though I feel this code can be further simplified, but it is my sleep time and my brain stops working 5 minutes ago,

data work.sample;

input id \$ primary \$ time \$;

datalines;

1 5  1

1 5  2

1 4  3

1 4  4

1 4  5

1 1  6

2 1  1

2 1  2

2 4  3

2 4  4

2 4  5

2 5  6

3 1  1

3 4  2

3 4  3

3 4  4

3 1  5

3 1  6

;

data want;

declare hash h(multidata:'y');

h.definekey('id');

h.definedata('id', 'primary','time', 'duration');

h.definedone();

declare hiter hi('h');

do until (last.id);

set sample;

retain flag;

_lg=lag(primary);

if primary='4' and _lg = '5' then flag=1;

if primary ne '4' and _lg = '4' then flag=0;

if primary='4' and flag ne 1 then

do;

duration+1;

end;

else if primary='5' and _lg='4' then

do;

do rc=hi.first() by 0 while (rc=0);

duration=0;

output;

rc=hi.next();

end;

rc=h.clear();

end;

else if primary ne '5' and _lg=4 then

do;

duration=0;

do rc=hi.first() by 0 while (rc=0);

output;

rc=hi.next();

end;

rc=h.clear();

end;

else

do;

duration=0;

output;

end;

end;

drop rc flag _lg;

run;

All Replies
Solution
‎03-22-2015 09:57 PM
Posts: 3,167

## Re: conditional counter (forward and backward looking)

Though I feel this code can be further simplified, but it is my sleep time and my brain stops working 5 minutes ago,

data work.sample;

input id \$ primary \$ time \$;

datalines;

1 5  1

1 5  2

1 4  3

1 4  4

1 4  5

1 1  6

2 1  1

2 1  2

2 4  3

2 4  4

2 4  5

2 5  6

3 1  1

3 4  2

3 4  3

3 4  4

3 1  5

3 1  6

;

data want;

declare hash h(multidata:'y');

h.definekey('id');

h.definedata('id', 'primary','time', 'duration');

h.definedone();

declare hiter hi('h');

do until (last.id);

set sample;

retain flag;

_lg=lag(primary);

if primary='4' and _lg = '5' then flag=1;

if primary ne '4' and _lg = '4' then flag=0;

if primary='4' and flag ne 1 then

do;

duration+1;

end;

else if primary='5' and _lg='4' then

do;

do rc=hi.first() by 0 while (rc=0);

duration=0;

output;

rc=hi.next();

end;

rc=h.clear();

end;

else if primary ne '5' and _lg=4 then

do;

duration=0;

do rc=hi.first() by 0 while (rc=0);

output;

rc=hi.next();

end;

rc=h.clear();

end;

else

do;

duration=0;

output;

end;

end;

drop rc flag _lg;

run;

New Contributor
Posts: 2

## Re: conditional counter (forward and backward looking)

Dear Hai.kuo,

thanks for the quick help! That code worked perfectly

Frequent Contributor
Posts: 115

## Re: conditional counter (forward and backward looking)

data work.sample;

input id \$ primary \$ time \$;

datalines;

1 5  1

1 5  2

1 4  3

1 4  4

1 4  5

1 1  6

2 1  1

2 1  2

2 4  3

2 4  4

2 4  5

2 5  6

3 1  1

3 4  2

3 4  3

3 4  4

3 1  5

3 1  6

;

data want;
call missing(k);
count=0;

do until(last.id);
set sample;
by id;
j=lag(primary);

if primary=4 then
count+1;

if count=1 then
k=j;

if primary ne 4 and lag(primary)=4 then
l=primary;
end;

do until(last.id);
set sample;
by id;

if primary=4 and k ne 5 and l ne 5 then
duration+1;
else duration=0;
output;
end;

keep id primary time duration;
run;

Regards,

Naveen Srinivasan

L&T Infotech

Frequent Contributor
Posts: 115

## Re: conditional counter (forward and backward looking)

Better formatted code:

data work.sample;

input id \$ primary \$ time \$;

datalines;

1 5  1

1 5  2

1 4  3

1 4  4

1 4  5

1 1  6

2 1  1

2 1  2

2 4  3

2 4  4

2 4  5

2 5  6

3 1  1

3 4  2

3 4  3

3 4  4

3 1  5

3 1  6

;

data want;
call missing(k);
count=0;

do until(last.id);
set sample;
by id;
j=lag(primary);

if primary=4 then
do;
count+1;

if count=1 then
k=j;
end;

if primary ne 4 and lag(primary)=4 then
l=primary;
end;

do until(last.id);
set sample;
by id;

if primary=4 and k ne 5 and l ne 5 then
duration+1;
else duration=0;
output;
end;

keep id primary time duration;
run;

Regards,

Naveen Srinivasan

L&T Infotech

🔒 This topic is solved and locked.