topic Re: Filling the gaps of a row depending on adjacent rows in SAS Data Management

Filling the gaps of a row depending on adjacent rows

ilikesas — Thu, 26 Feb 2015 14:35:03 GMT

Hi,

suppose I have the following table:

Company	Year	Cond1	Cond2
A	2006	23	44
A	2008	23	44
B	2007	1	2
B	2009	3	4

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:

Company	Year	Cond1	Cond2
A	2006	23	44
A	2007	23	44
A	2008	23	44
B	2007	1	2
B	2008	3	4

Thank you!

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

RW9 — Thu, 26 Feb 2015 14:55:02 GMT

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.

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

art297 — Mon, 02 Mar 2015 22:49:50 GMT

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;

else if year+&factor lt nextyear then do;

do year=year to year+&factor;

flag=2;

output;

end;

else do;

flag=3;

output;

end;

run;

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

ilikesas — Tue, 03 Mar 2015 00:27:55 GMT

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

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

art297 — Tue, 03 Mar 2015 00:32:41 GMT

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

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

ilikesas — Tue, 03 Mar 2015 00:34:21 GMT

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

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

art297 — Tue, 03 Mar 2015 00:43:48 GMT

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.

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

ilikesas — Tue, 03 Mar 2015 01:35:25 GMT

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?

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

art297 — Tue, 03 Mar 2015 01:46:55 GMT

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:

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

ilikesas — Tue, 03 Mar 2015 02:29:14 GMT

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

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

art297 — Tue, 03 Mar 2015 02:46:29 GMT

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

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

data_null__ — Tue, 03 Mar 2015 15:05:29 GMT

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 cond:); /*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; 
 

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

Community_Help — Tue, 03 Mar 2015 16:02:09 GMT

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.