Filling the gaps 3 - different conditions for adjacent rows

[ilikesas]

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:

company	year	cond1	cond2
A	2006	1	2
A	2009	1	2
B	2005	3	4
B	2010	3	6
B	2012	3	6

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:

company	year	cond1	cond2
A	2006	1	2
A	2009	1	2
B	2005	3	4
B	2006	3	4
B	2010	3	6
B	2012	3	6

Thank you!

[art297 · Accepted solution]

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;

[ilikesas]

Thanks Arthur for your answer!

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!

[art297]

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.