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;
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: