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!
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.