topic Filling the time gap bw cells 2 (where conditions are IDENTICAL) in SAS Data Management

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

ilikesas — Tue, 03 Mar 2015 15:02:30 GMT

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!

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

art297 — Tue, 03 Mar 2015 15:50:47 GMT

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;

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

Haikuo — Tue, 03 Mar 2015 16:26:27 GMT

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;

else

do;

i=year;

output;

end;

drop year _:;

rename i=year;

run;