## Add semi-annual variable to existing table

Solved
Occasional Contributor
Posts: 8

# Add semi-annual variable to existing table

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

Accepted Solutions
Solution
‎08-23-2017 05:26 PM
Posts: 5,484

## Re: Add semi-annual variable to existing table

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;``````
PG

All Replies
Solution
‎08-23-2017 05:26 PM
Posts: 5,484

## Re: Add semi-annual variable to existing table

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;``````
PG
☑ This topic is solved.