BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lochen
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

1 REPLY 1
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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