Help using Base SAS procedures

Creating New Records using Retain and If Statements

Reply
Occasional Contributor
Posts: 16

Creating New Records using Retain and If Statements

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.
PROC Star
Posts: 7,492

Re: Creating New Records using Retain and If Statements

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. Smiley Happy;
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.
Regular Contributor
Posts: 184

Re: Creating New Records using Retain and If Statements

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.
Ask a Question
Discussion stats
  • 2 replies
  • 179 views
  • 0 likes
  • 3 in conversation