Looking back values of variables within one year

[DathanMD]

Hi SAS experts,

I am SAS starter and I need your help with some code. I have a dataset of students (id) and their weights over a period (date). I would like to create another variable (Lag_weight_year) that records weight that is more than one year ago but within two years of the current date (date). So, in essence, I will want weights between one year-two year prior to the current date

*The data I have;

Data have;
Format dates date9.;
input Id Dates:date9. weight;
cards;
1 3/01/2012 64
1 29/05/2012 87
1 16/10/2012 87
1 2/04/2013 88
2 20/08/2013 88
2 4/02/2014 88
2 13/05/2014 90
2 15/07/2014 90
2 14/12/2015 78
2 16/12/2015 76
2 14/02/2018 76
3 3/01/2012 85
3 29/05/2013 64
3 16/10/2014 87
;
Run;

*The dataset i want (want);

data want;

Format dates date9.;

input Id Dates:date9. weight Lag_weight_year;

cards;

1 3/01/2012 64 .

1 29/05/2012 87 .

1 16/10/2012 87 .

1 2/04/2013 88 64

2 20/08/2013 88 .

2 4/02/2014 88 .

2 13/05/2014 90 88

2 15/07/2014 90 88

2 14/12/2015 78 90

2 16/12/2015 76 90

2 14/02/2018 76 .

3 3/01/2012 85 .

3 29/05/2013 64 85

3 16/10/2014 87 64

;

run;

Thank you so much.

Dathan Byonanebye

[mkeintz]

If a current date has multiple prior dates that fall between 1 and 2 years prior, which of those prior weights do you want?

editted additional question:  

Also, why does the third record of ID=2 (13/05/2014) in your expected output have a lag_weight_value=88?  Neither of the two preceding dates (20/08/2013 and 4/02/2014) are more than 1 year prior.

[DathanMD]

Thanks for asking. In that case, i would like the latest of those multiple dates. The one closest to the current date 

[DathanMD]

This is my oversight, ignore that entry

[mkeintz · Accepted solution]

By "between one year-two year prior" I assume you mean any date from exactly one year prior up to but not including exactly two years prior.  That range includes all cases in which the function

intck('year',from_date,to_date,'continuous')

returns a value of 1.

Data have;
Format dates date9.;
input Id Dates:ddmmyy10. weight;
cards;
1 3/01/2012 64
1 29/05/2012 87
1 16/10/2012 87
1 2/04/2013 88
2 20/08/2013 88
2 4/02/2014 88
2 13/05/2014 90
2 15/07/2014 90
2 14/12/2015 78
2 16/12/2015 76
2 14/02/2018 76
3 3/01/2012 85
3 29/05/2013 64
3 16/10/2014 87
Run;

data want (drop=_:);
  set have;
  by id;
  array date_history {20} _temporary_;
  array weight_history {20} _temporary_;
  if first.id then do;
    call missing(of weight_history{*}, of date_history{*});
    _seq=1;
  end;
  else _seq+1;

  weight_history{_seq}=weight;
  date_history{_seq}=dates;

  if _seq>1 then do _s=_seq-1 to 1 by -1 until (intck('year',date_history{_s},dates,'continuous')=1);
  end;
  if _s>0 then lag_weight_year=weight_history{_s};
  if _s>0 then date_of_lag_weight_year=date_history{_s};
  format date_of_lag_weight_year date9.;
run;

Thank you for providing sample data in the format of a data step.  It wasn't quite a working data step, but changing the informat used for variable dates in the input statement fixed it.

[DathanMD]

Thank you so much. Works well