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

[ilikesas]

Hi,

suppose I have the following table:

company	year	cond1	cond2
A	2006	1	2
A	2008	1	2
A	2010	3	4
B	2000	11	22
B	2010	0	0

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

company	year	cond1	cond2
A	2006	1	2
A	2007	1	2
A	2008	1	2
A	2010	3	4
B	2000	11	22
B	2010	0	0

Thank you!

[art297 · Accepted solution]

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;

[Haikuo]

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;