DATA Step, Macro, Functions and more

Add semi-annual variable to existing table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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
Respected Advisor
Posts: 4,925

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

View solution in original post


All Replies
Solution
‎08-23-2017 05:26 PM
Respected Advisor
Posts: 4,925

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 54 views
  • 0 likes
  • 2 in conversation