## Filling the gaps 3 - different conditions for adjacent rows

Solved
Super Contributor
Posts: 459

# Filling the gaps 3 - different conditions for adjacent rows

Hi,

This is the 3rd question that I am posting from the same series - its too much to put into one question so I decided to break into several questions.

Suppose I have the following table:

companyyearcond1cond2
A200612
A200912
B200534
B201036
B201236

In this case if two adjacent years for the same company have the same conditions then leave as is.

If two adjacent years have different conditions, then the year after the first year will have the same conditions as the first year. In this example, for Bditional year 2006 should be created with conditions (3,4)

So the new table should look like:

companyyearcond1cond2
A200612
A200912
B200534
B200634
B201036
B201236

Thank you!

Accepted Solutions
Solution
‎03-06-2015 08:35 PM
PROC Star
Posts: 8,146

## Re: Filling the gaps 3 - different conditions for adjacent rows

Next time attempt to solve the problem BEFORE asking for advice. You should have been able to figure this out by now:

data have;

infile cards dlm='09'x;

input company \$ year cond1 cond2;

cards;

A 2006 1 2

A 2009 1 2

B 2005 3 4

B 2010 3 6

B 2012 3 6

;

data want;

set have end=eof;

by company cond1 cond2 notsorted;

if last.cond2 and not last.company then do;

set have(firstobs=2 keep=year rename=year=nextyear);

output;

if year+1 ne nextyear then do;

year=year+1;

output;

end;

end;

else output;

drop nextyear;

run;

All Replies
Solution
‎03-06-2015 08:35 PM
PROC Star
Posts: 8,146

## Re: Filling the gaps 3 - different conditions for adjacent rows

Next time attempt to solve the problem BEFORE asking for advice. You should have been able to figure this out by now:

data have;

infile cards dlm='09'x;

input company \$ year cond1 cond2;

cards;

A 2006 1 2

A 2009 1 2

B 2005 3 4

B 2010 3 6

B 2012 3 6

;

data want;

set have end=eof;

by company cond1 cond2 notsorted;

if last.cond2 and not last.company then do;

set have(firstobs=2 keep=year rename=year=nextyear);

output;

if year+1 ne nextyear then do;

year=year+1;

output;

end;

end;

else output;

drop nextyear;

run;

Super Contributor
Posts: 459

## Re: Filling the gaps 3 - different conditions for adjacent rows

I know that it is very similar to my previous questions, its just that I have some trouble intuiting the code, but I am trying to figure that out

Thanks again!

PROC Star
Posts: 8,146

## Re: Filling the gaps 3 - different conditions for adjacent rows

Just think through the process. In the present case if the data are already sorted by company and year, and then set (in the data step) with a by statement (namely by company cond1 cond2 notsorted), then:

last.company will only be equal to 1 when the data step gets to the last record for each company.

last.cond2 will only be equal to 1 when the data step get to a record where it is either the last record for a company or where the next record for a company has a different cond1 or cond2

set have(firstobs=2 keep=year rename=year=nextyear); creates a variable called nextyear that shows the year shown on the next record.

Thus, the code outputs all records and, if the current record isn't the last record for a company AND the next record has a different cond1 or cond2 AND if the next record's year is greater than year+1, then output a new record with year=year+1.

🔒 This topic is solved and locked.