topic Re: conditional counter (forward and backward looking) in SAS Procedures

conditional counter (forward and backward looking)

flmn — Sun, 22 Mar 2015 20:58:55 GMT

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!

Re: conditional counter (forward and backward looking)

Haikuo — Mon, 23 Mar 2015 01:57:30 GMT

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;

rc=h.add();

end;

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

do;

rc=h.add();

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;

rc=h.add();

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

output;

rc=hi.next();

end;

rc=h.clear();

end;

else

do;

duration=0;

output;

end;

drop rc flag _lg;

run;

Re: conditional counter (forward and backward looking)

flmn — Mon, 23 Mar 2015 08:00:15 GMT

Dear Hai.kuo,

thanks for the quick help! That code worked perfectly

Re: conditional counter (forward and backward looking)

naveen_srini — Mon, 23 Mar 2015 08:53:22 GMT

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

Re: conditional counter (forward and backward looking)

naveen_srini — Mon, 23 Mar 2015 09:12:55 GMT

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