Hi,
I have a dataset based on date like the fowlloing
DATE | Value
16APR2013 1
25APR2013 1
21MAY2013 2
05Jun2013 4
07Sep2013 5
10Oct2013 5
21Dec2013 8
03Feb2014 7
08Mar2014 9
01Jun2014 10
I need some help to add a new variable only records the semi-annual value based on its most close previous date value.
If the semi-year date doesn't exist in table, I need to add one.
Here is what I expect the result looks like
DATE | Value | Semi-year
01Jan2013 0 0
16APR2013 1 .
25APR2013 1 .
21MAY2013 2 .
01Jun2013 2 2
05Jun2013 4 .
07Sep2013 5 .
10Oct2013 5 .
21Dec2013 8 .
01Jan2014 8 8
03Feb2014 7 .
08Mar2014 9 .
01Jun2014 10 10
In a single step, you could do:
data have;
input DATE :date9. Value;
format date date9.;
datalines;
16APR2013 1
25APR2013 1
21MAY2013 2
05Jun2013 4
07Sep2013 5
10Oct2013 5
21Dec2013 8
03Feb2014 7
08Mar2014 9
01Jul2014 10
;
data want;
set have(rename=(date=_date value=_value));
_prevValue = lag(_value);
_semiYearDate = intnx("semiYear", _date, 0);
if _semiYearDate ne lag(_semiYearDate) then
if _semiYearDate = _date then
semiYearValue = _value;
else do;
semiYearValue = coalesce(_prevValue,0);
value = semiYearValue;
date = _semiYearDate;
output;
call missing(semiYearValue);
end;
date = _date;
value = _value;
output;
drop _: ;
format date date9.;
run;
proc print noobs; run;
In a single step, you could do:
data have;
input DATE :date9. Value;
format date date9.;
datalines;
16APR2013 1
25APR2013 1
21MAY2013 2
05Jun2013 4
07Sep2013 5
10Oct2013 5
21Dec2013 8
03Feb2014 7
08Mar2014 9
01Jul2014 10
;
data want;
set have(rename=(date=_date value=_value));
_prevValue = lag(_value);
_semiYearDate = intnx("semiYear", _date, 0);
if _semiYearDate ne lag(_semiYearDate) then
if _semiYearDate = _date then
semiYearValue = _value;
else do;
semiYearValue = coalesce(_prevValue,0);
value = semiYearValue;
date = _semiYearDate;
output;
call missing(semiYearValue);
end;
date = _date;
value = _value;
output;
drop _: ;
format date date9.;
run;
proc print noobs; run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.