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

Filling the gaps of a row depending on adjacent rows

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

Filling the gaps of a row depending on adjacent rows

Hi,

suppose I have the following table:

CompanyYearCond1Cond2
A20062344
A20082344
B200712
B200934

 

In the table above, there are some gaps: some years are missing for each company. But for company A, the conditions for both years 2006 and 2008 are the same, so what I would like to do is to add a new row for the company A for the year 2007 with the same conditions, but for company B remain as is because the conditions are different (so don't add year 2008 for B).

The new table should be like this:

CompanyYearCond1Cond2
A20062344
A20072344
A20082344
B200712
B200834

Thank you!


Accepted Solutions
Solution
‎03-02-2015 05:49 PM
PROC Star
Posts: 7,360

Re: Filling the gaps of a row depending on adjacent rows

Daniel,

Here is a slightly different approach that seems to work on both of your example datasets:

%let factor=1;

data need;

  set have end=eof;

  by company cond1 cond2 notsorted;

  if not eof then set have(firstobs=2 keep=year cond1 cond2

                           rename=(cond1=nextcond1

                                   cond2=nextcond2

                                   year=nextyear)); 

  if last.company then do;

    flag=4;

    output;

  end;

  else if cond1 eq nextcond1 and cond2 eq nextcond2 then do;

    do year=year to nextyear-1;

      flag=1;

      output;

    end;

  end;

  else if year+&factor lt nextyear then do;

    do year=year to year+&factor;

      flag=2;

      output;

    end;

  end;

   else do;

    flag=3;

    output;

  end;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: Filling the gaps of a row depending on adjacent rows

Hi,

Can you clarify your test data as in the first table you have 2007 and 2009 for B, but in the second you have 2007 and 2008.

The easiest way I see of doing this is to create a dataset with distinct Company, min(year), max(year), then call execute() some statements to create then output table.

Solution
‎03-02-2015 05:49 PM
PROC Star
Posts: 7,360

Re: Filling the gaps of a row depending on adjacent rows

Daniel,

Here is a slightly different approach that seems to work on both of your example datasets:

%let factor=1;

data need;

  set have end=eof;

  by company cond1 cond2 notsorted;

  if not eof then set have(firstobs=2 keep=year cond1 cond2

                           rename=(cond1=nextcond1

                                   cond2=nextcond2

                                   year=nextyear)); 

  if last.company then do;

    flag=4;

    output;

  end;

  else if cond1 eq nextcond1 and cond2 eq nextcond2 then do;

    do year=year to nextyear-1;

      flag=1;

      output;

    end;

  end;

  else if year+&factor lt nextyear then do;

    do year=year to year+&factor;

      flag=2;

      output;

    end;

  end;

   else do;

    flag=3;

    output;

  end;

run;

Super Contributor
Posts: 413

Re: Filling the gaps of a row depending on adjacent rows

Hi Arthur,

I deleted my previous reply about the code not working for my second data.

In reality it didn't work on the second data after I ran it on the first data, but when I restarted my SAS program and ran the code immediately on the second data it worked perfectly, and the same thing for data_null's code.

Maybe I had a problem because of the %let factor=1; because SAS didn't re=assign it when I ran the code on the second data right after I ran it on the first data?

And one very last thing for this discussion, could you please re-upload the code that fills the years ONLY in the case where the adjacent years (for a given company) have the same conditions?

thanks

PROC Star
Posts: 7,360

Re: Filling the gaps of a row depending on adjacent rows

Daniel: For future reference .. you should NEVER delete a post to which others have responded.

The main purpose of the forum is to help people learn how to use SAS, and the history of how a problem is solved (including everyone's suggested solutions) is critical to everyone's learning path.

When you delete a post, all of those responses are lost Smiley Sad

Super Contributor
Posts: 413

Re: Filling the gaps of a row depending on adjacent rows

sorry, just thought that it will be less messy that way...

I also edited my last reply with a small request if I may...

thanks

PROC Star
Posts: 7,360

Re: Filling the gaps of a row depending on adjacent rows

Post your new question by starting a new discussion. And, in doing so, provide example have and want datasets, as well as explain what you are NOW looking for. If your last question is answered, mark it as having been answered.

Super Contributor
Posts: 413

Re: Filling the gaps of a row depending on adjacent rows

was wandering, if I delete my message do all the replies to it also get deleted? and if so is it possible to reinstate what I deleted?

PROC Star
Posts: 7,360

Re: Filling the gaps of a row depending on adjacent rows

Yes, all responses get deleted. If there is a way to reinstate them, I'm not aware of it.

However, I know someone who would know:

Super Contributor
Posts: 413

Re: Filling the gaps of a row depending on adjacent rows

I tried to send communities admin a direct message but wasn't allowed, is there another way I can reach admin?

PROC Star
Posts: 7,360

Re: Filling the gaps of a row depending on adjacent rows

She already received it based on me identifying her in my last message.

SAS Employee
Posts: 232

Re: Filling the gaps of a row depending on adjacent rows

Hi there - my apologies for the late response. It's true unfortunately if you delete your message we are unable to reinstate it. I'm so sorry.

Respected Advisor
Posts: 3,777

Re: Filling the gaps of a row depending on adjacent rows

Maybe I understand now.  I wasn't paying attention after the second time you changed you mind.:smileyshocked:

I added observations for the years that are not filled and set COND1 and 2 to missing.  I also identified the original obs to help with checking.

data have(sortedby=company year);
   infile cards expandtabs;
  
input Company :$1. Year Cond1 Cond2;
   cards;
A  2006  23 44
A  2008  23 44
B  2007  1  2
B  2009  3  4
C  2006  23 44
C  2007  23 44
C  2008  1  2
C  2010  3  4
C  2011  23 44
C  2014  23 44
D  2006  23 44
D  2009  23 44
D  2010  23 44
D  2012  23 44
E  2005   3  6
E  2009   5  6
E  2010   5  6
F  2006 23 44
F  2009 23 44
F  2013 1  2
G  2006 10 20
G  2008 10 20
H  2006 23 44
H  2009 23 44
H  2011 23 44
H  2014 1  2
;;;;
   run;
proc print;
  
by company;
   id company;
   run;
%let factor=1;
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 last.company then do; flag=4; output; end;
  
else if not last.cond2 then do year=year to nextyear-1; /*Cond1 and Cond2 eq NEXT obs Cond1 and Cond2*/
      flag=
3;
     
output;
     
end;
  
else if last.cond2 then do; /*Does not equal next obs*/
      syear = year;
      do year=year to nextyear-1;
        
if year gt syear+&factor then call missing(of condSmiley Happy; /*Fill all years but set cond 1 and 2 to missing after factor year*/
         flag=
1;
        
output;
        
end;
     
end;
  
drop nextyear syear;
   run;
data need;
   merge have(in=in1 keep=company year) need;
   by company year;
   Length Source $8;
   Source = ifc(in1,
'Original','Filled');
   run;
proc print;
  
by company;
   id company;
   run;


3-3-2015 9-01-34 AM.png3-3-2015 9-03-47 AM.png
☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 766 views
  • 3 likes
  • 5 in conversation