Creating New Records using Retain and If Statements

[SamT]

Hello,

I was wondering if you may be able to help me.

I'm trying to create new records between existing. What I've got is records with an Id, a Value, start date and and end date, so for example 001, $5000, 01JUN2009 to 21OCT2009. Then the next is 001, $4700, 22OCT2009 to 15DEC2009.

What I'd like to do is create a new record counted by month in between the start and end date and then also assign a new value in between, so something like...

001, $5000, 01JUN2009, 01JUL2009.
001, $4900, 01JUL2009 to 01AUG2009.
001, $4800, 01AUG2009 to 01SEP2009.
001, $4700, 01SEP2009 to 01OCT2009.

What I've been exploring is something along the lines of...

Proc Sort data=Sample;
by id startdate;
run;

Data Gaps;
Set Sample;
By Id startdate;
Retain Startdate Enddate Value;
If First.Id then output;
else do;
Month_Gap = INTCK( 'month', Startdate, Enddate);
if Month_Gap = 0 then do;
StartDate1= intnx('month',Startdate,0,'beginning');
EndDate1 = intnx('month',Enddate,1,'beginning');
output;
end;
.........
..........

I know what that produces and it's certainly not the desired result but that's what I've been playing around with trying to work it out.

[art297]

Sam,

The following definitely ISN'T tested code, but it should be close enough to allow you to figure out how to get what you want:
[pre]
data sample;
input id $3. value dollar8.0 (startdate enddate) (date9. :);
cards;
001 $5000. 01JUN2009 1JUL2009.
001 $4700. 01SEP2009 01OCT2009.
;

proc sort data=Sample;
by id startdate;
run;

data gaps (keep=id startdate enddate value);
set sample (rename=(
startdate=current_startdate
enddate=current_enddate
value=current_value
))
;
by Id;
format startdate enddate date9.;
retain last_startdate last_enddate last_value;
if first.Id then do;
call missing(last_startdate);
call missing(last_enddate);
call missing(last_value);
startdate=current_startdate;
enddate=current_enddate;
value=current_value;
output;
end;
else do;
value_increment=(current_value-last_value)/
(INTCK( 'month', last_enddate,current_startdate)+1);
do i=0 to INTCK( 'month', last_enddate,
current_startdate);
startdate= intnx('month',last_startdate,i+1,'beginning');
enddate = intnx('month',last_enddate,i+1,'beginning');
value=last_value+(i+1)*value_increment;
output;
end;
end;
last_startdate=current_startdate;
last_enddate=current_enddate;
last_value=current_value;
run;
[/pre]
HTH,
Art
--------
> Hello,
>
> I was wondering if you may be able to help me.
>
> I'm trying to create new records between existing.
> What I've got is records with an Id, a Value, start
> date and and end date, so for example 001, $5000,
> 01JUN2009 to 21OCT2009. Then the next is 001, $4700,
> 22OCT2009 to 15DEC2009.
>
> What I'd like to do is create a new record counted by
> month in between the start and end date and then also
> assign a new value in between, so something like...
>
>
> 001, $5000, 01JUN2009, 01JUL2009.
> 001, $4900, 01JUL2009 to 01AUG2009.
> 001, $4800, 01AUG2009 to 01SEP2009.
> 001, $4700, 01SEP2009 to 01OCT2009.
>
> What I've been exploring is something along the lines
> of...
>
> Proc Sort data=Sample;
> by id startdate;
> run;
>
> Data Gaps;
> Set Sample;
> By Id startdate;
> Retain Startdate Enddate Value;
> If First.Id then output;
> else do;
> Month_Gap = INTCK( 'month', Startdate, Enddate);
> if Month_Gap = 0 then do;
> StartDate1= intnx('month',Startdate,0,'beginning');
> EndDate1 = intnx('month',Enddate,1,'beginning');
> output;
> end;
> .........
> ..........
>
> I know what that produces and it's certainly not the
> desired result but that's what I've been playing
> around with trying to work it out.

[Howles]

This is known as interpolation. If you have SAS/ETS licensed, look at PROC EXPAND.

> Hello,
>
> I was wondering if you may be able to help me.
>
> I'm trying to create new records between existing.
> What I've got is records with an Id, a Value, start
> date and and end date, so for example 001, $5000,
> 01JUN2009 to 21OCT2009. Then the next is 001, $4700,
> 22OCT2009 to 15DEC2009.
>
> What I'd like to do is create a new record counted by
> month in between the start and end date and then also
> assign a new value in between, so something like...
>
>
> 001, $5000, 01JUN2009, 01JUL2009.
> 001, $4900, 01JUL2009 to 01AUG2009.
> 001, $4800, 01AUG2009 to 01SEP2009.
> 001, $4700, 01SEP2009 to 01OCT2009.
>
> What I've been exploring is something along the lines
> of...
>
> Proc Sort data=Sample;
> by id startdate;
> run;
>
> Data Gaps;
> Set Sample;
> By Id startdate;
> Retain Startdate Enddate Value;
> If First.Id then output;
> else do;
> Month_Gap = INTCK( 'month', Startdate, Enddate);
> if Month_Gap = 0 then do;
> StartDate1= intnx('month',Startdate,0,'beginning');
> EndDate1 = intnx('month',Enddate,1,'beginning');
> output;
> end;
> .........
> ..........
>
> I know what that produces and it's certainly not the
> desired result but that's what I've been playing
> around with trying to work it out.