topic Re: Add semi-annual variable to existing table in SAS Programming

Add semi-annual variable to existing table

Lochen — Tue, 22 Aug 2017 19:28:52 GMT

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

Re: Add semi-annual variable to existing table

PGStats — Tue, 22 Aug 2017 20:55:19 GMT

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;