SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Filling the gaps 3 - different conditions for adjacent rows

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

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: 7,468

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;

View solution in original post


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

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: 441

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

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!

PROC Star
Posts: 7,468

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 308 views
  • 3 likes
  • 2 in conversation