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

Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Hi,

suppose I have the following table:

companyyearcond1cond2
A200612
A200812
A201034
B20001122
B201000

If 2 adjacent years for the same company have the SAME conditions, then all the years (if they are missing) bw these adjacent years should be created also having those conditions - in this case for comp A create year 2007 (1,2).

If adjacent years for the same company have different conditions then keep as is.

So the want table is like this

companyyearcond1cond2
A200612
A200712
A200812
A201034
B20001122
B201000

Thank you!


Accepted Solutions
Solution
‎03-03-2015 10:50 AM
PROC Star
Posts: 7,358

Re: Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Daniel,

What you want to accomplish only requires incorporating one condition of the code that data_null_ suggested. e.g.:

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 not last.cond2 then do year=year to nextyear-1;

      output;

   end;

   else output;

   drop nextyear;

run;

View solution in original post


All Replies
Solution
‎03-03-2015 10:50 AM
PROC Star
Posts: 7,358

Re: Filling the time gap bw cells 2 (where conditions are IDENTICAL)

Daniel,

What you want to accomplish only requires incorporating one condition of the code that data_null_ suggested. e.g.:

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 not last.cond2 then do year=year to nextyear-1;

      output;

   end;

   else output;

   drop nextyear;

run;

Respected Advisor
Posts: 3,124

Re: Filling the time gap bw cells 2 (where conditions are IDENTICAL)

An alternative to looking ahead technique. This is looking-back.

data have;

      input company$  year cond1 cond2;

     cards;

A    2006 1    2

A    2008 1    2

A    2010 3    4

A    2011 3    4

B    2000 11   22

B    2010 0    0

;

data want;

     set have;

     by company year;

     _lagY=lag(year);

     _lagC1=lag(cond1);

     _lagC2=lag(cond2);

     if first.company then

           do;

                i=year;

                output;

           end;

     else

           do;

                if cond1=_lagC1 and cond2=_lagC2 then

                     do;

                           do i=_lagY+1 to year;

                                output;

                           end;

                     end;

                else

                     do;

                           i=year;

                           output;

                     end;

           end;

           drop year _:;

           rename i=year;

run;

☑ This topic is SOLVED.

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

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