Add semi-annual variable to existing table

[Lochen]

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

[PGStats · Accepted solution]

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;