## Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Solved
Super Contributor
Posts: 459

# Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Hi,

suppose I have the following table:

companyyearcond1cond2
A200612
A200812
A201034
B20001122
B201000

If 2 adjacent years for the same company have the SAME conditions, then all the years (if they are missing) bw these adjacent years should be created also having those conditions - in this case for comp A create year 2007 (1,2).

If adjacent years for the same company have different conditions then keep as is.

So the want table is like this

companyyearcond1cond2
A200612
A200712
A200812
A201034
B20001122
B201000

Thank you!

Accepted Solutions
Solution
‎03-03-2015 10:50 AM
PROC Star
Posts: 8,149

## Re: Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Daniel,

What you want to accomplish only requires incorporating one condition of the code that data_null_ suggested. e.g.:

data need;

set have end=eof;

by company cond1 cond2 notsorted;

if not eof then set have(firstobs=2 keep=year rename=year=nextyear);

if not last.cond2 then do year=year to nextyear-1;

output;

end;

else output;

drop nextyear;

run;

All Replies
Solution
‎03-03-2015 10:50 AM
PROC Star
Posts: 8,149

## Re: Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Daniel,

What you want to accomplish only requires incorporating one condition of the code that data_null_ suggested. e.g.:

data need;

set have end=eof;

by company cond1 cond2 notsorted;

if not eof then set have(firstobs=2 keep=year rename=year=nextyear);

if not last.cond2 then do year=year to nextyear-1;

output;

end;

else output;

drop nextyear;

run;

Posts: 3,167

## Re: Filling the time gap bw cells 2 (where conditions are IDENTICAL)

An alternative to looking ahead technique. This is looking-back.

data have;

input company\$  year cond1 cond2;

cards;

A    2006 1    2

A    2008 1    2

A    2010 3    4

A    2011 3    4

B    2000 11   22

B    2010 0    0

;

data want;

set have;

by company year;

_lagY=lag(year);

_lagC1=lag(cond1);

_lagC2=lag(cond2);

if first.company then

do;

i=year;

output;

end;

else

do;

if cond1=_lagC1 and cond2=_lagC2 then

do;

do i=_lagY+1 to year;

output;

end;

end;

else

do;

i=year;

output;

end;

end;

drop year _:;

rename i=year;

run;

🔒 This topic is solved and locked.