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
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.
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.
Thanks for asking. In that case, i would like the latest of those multiple dates. The one closest to the current date
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.
Thank you so much. Works well
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: