Using values from next group of observations to populate previous group's values

[Mk601]

Hi,

In by group processing, I am trying to backfill previous group's values with the values available for next group. An example will be more helpful in illustrating what I am trying to do

Have		Want
ID1	ID2	Date		ID1	ID2	Date	Date_
0001	10	20051231		0001	10	20051231	20061231
0001	20	20051231		0001	20	20051231	20061231
0001	30	20051231		0001	30	20051231	20061231
0001	40	20051231		0001	40	20051231	20061231
0001	10	20061231		0001	10	20061231	20071231
0001	20	20061231		0001	20	20061231	20071231
0001	30	20061231		0001	30	20061231	20071231
0001	40	20061231		0001	40	20061231	20071231
0001	10	20071231		0001	10	20071231	20071231
0001	20	20071231		0001	20	20071231	20071231
0001	30	20071231		0001	30	20071231	20071231
0001	40	20071231		0001	40	20071231	20071231

The groups are formed by ID1 and ID2 and the date column is the same for all observations within the group. As the 'want' table shows, the date value from next group of observation is backfilled to previous group of observations. For the last group, date and date_ columns have the same value. Would appreciate any help on how to achieve this.

Thanks.

[PeterClemmensen]

Hi and welcome to the SAS Communities 🙂

Is your problem as simple as your data here suggests? In that case:

data have;
input ID1 $ ID2 $ Date:yymmdd8.;
format Date yymmdd10.;
datalines;
0001 10 20051231
0001 20 20051231
0001 30 20051231
0001 40 20051231
0001 10 20061231
0001 20 20061231
0001 30 20061231
0001 40 20061231
0001 10 20071231
0001 20 20071231
0001 30 20071231
0001 40 20071231
;

data want;
   merge have have(firstobs=5 keep=Date rename=(Date=Date_));
   if Date_=. then Date_=Date;
run;

[Mk601]

Hi Draycut,

Thanks for the welcome and response to my query. As you mentioned, the actual data is not as simple as the snippet I posted here. Is it possible to make the value of firstobs dynamic. The reason being, for other groups of observations that value could be different.

Thanks. 

[PGStats · Accepted solution]

If your data is sorted by ID1 and date:

data want;
do until(last.date);
    set have nobs=nobs; by id1 date;
    obs + 1;
    end;
point = obs + 1;
if point <= nobs and not last.id1 then set have point=point;
_Date = date;
format _Date yymmdd10.;
do until(last.date);
    set have; by id1 date;
    output;
    end;
drop obs;
run;

[Tom]

I don't understand how you are defining your groups. You seem to be saying that the groups are defined by the date. 

What impact to the values of ID and ID2 have on defining the groups?

Based on looking at the dates there appear to be three groups in your example data.

Perhaps an easy way to think of the problem is to first create a variable to indicate the group number.  Then you can take the date from the next group.

data have;
  input ID1 $ ID2 $ Date WantDate2 ;
  informat date wantdate2 yymmdd.;
  format date wantdate2 yymmdd10.;
cards;
0001  10  20051231  20061231
0001  20  20051231  20061231
0001  30  20051231  20061231
0001  40  20051231  20061231
0001  10  20061231  20071231
0001  20  20061231  20071231
0001  30  20061231  20071231
0001  40  20061231  20071231
0001  10  20071231  20071231
0001  20  20071231  20071231
0001  30  20071231  20071231
0001  40  20071231  20071231
;

data groups;
  set have;
  by date;
  groupid+first.date;
run;

proc sql noprint;
create table want as
  select *,b.date as date2 format=yymmdd10.
  from groups a 
  left join (select groupid,min(date) as date from groups group by groupid) b
  on a.groupid= b.groupid-1
;
quit;

Obs    ID1     ID2          Date     WantDate2    groupid         date2

  1    0001    30     2005-12-31    2006-12-31       1       2006-12-31
  2    0001    40     2005-12-31    2006-12-31       1       2006-12-31
  3    0001    10     2005-12-31    2006-12-31       1       2006-12-31
  4    0001    20     2005-12-31    2006-12-31       1       2006-12-31
  5    0001    30     2006-12-31    2007-12-31       2       2007-12-31
  6    0001    20     2006-12-31    2007-12-31       2       2007-12-31
  7    0001    10     2006-12-31    2007-12-31       2       2007-12-31
  8    0001    40     2006-12-31    2007-12-31       2       2007-12-31
  9    0001    40     2007-12-31    2007-12-31       3                .
 10    0001    30     2007-12-31    2007-12-31       3                .
 11    0001    20     2007-12-31    2007-12-31       3                .
 12    0001    10     2007-12-31    2007-12-31       3                .

Note that there is no way to find a value for the new variable for the last group.

[Mk601]

Hi Tom,

Thanks for your response. Groups of observations are created by ID1 and ID2.

[Tom]

Then show more clearly what you are talking about as your initial display is NOT grouped by ID and ID2.

Can there be more than on observation per ID/ID2/DATE group?

If not then it sounds like you just want to do a simple "lead" operation.

data want ;
  set have ;
  by id id2 date;
  set have(keep=date rename=(date=date2) firstobs=2) have(obs=1 drop=_all_);
  if last.id2 then call missing(date2);
run;