Propagate a row untill next date value and continue till certain date

[Sandy10]

Hi,

There is a dataset as below. I would like to retain the full row untill there is a change in the date value and continue the same till a particular date.

Dataset:

data bal_det;
informat st_dt open_dt date9.;
format st_dt open_dt date9.;
input id $ st_dt open_dt bal;
cards;

1 19SEP2017 19SEP2017 10000
1 19DEC2017 19SEP2017 0
3 29DEC2017 29DEC2017 35000
3 30DEC2017 29DEC2017 0
3 01JAN2018 29DEC2017 1000
3 10JAN2018 29DEC2017 30000
3 27FEB2018 29DEC2017 0
3 10MAR2018 29DEC2017 1000
;

Desired result:

id st_dt              open_dt     bal
1 19SEP2017 19SEP2017 10000
1 20SEP2017 19SEP2017 10000
1 21SEP2017 19SEP2017 10000
1 .....                                    10000
1 18DEC2017 19SEP2017 10000
1 19DEC2017 19SEP2017 0
1 20DEC2017 19SEP2017 0
1 21DEC2017 19SEP2017 0
1 .....                                    0
1 31MAR2018 19SEP2017 0
3 29DEC2017 29DEC2017 35000
3 30DEC2017 29DEC2017 0
3 31DEC2017 29DEC2017 0
3 01JAN2018 29DEC2017 1000
3 02JAN2018 29DEC2017 1000
3 ....                                     1000
3 09JAN2018 29DEC2017 1000
3 10JAN2018 29DEC2017 30000
3 11JAN2018 29DEC2017 30000
3 12JAN2018 29DEC2017 30000
3 ....                                    30000
3 26FEB2018 29DEC2017 30000
3 27FEB2018 29DEC2017 0
3 28FEB2018 29DEC2017 0
3 ...                                      0
3 09MAR2018 29DEC2017 0
3 ....                                     0
3 09MAR2018 29DEC2017 0
3 10MAR2018 29DEC2017 1000
3 .....                                    1000
3 31MAR2018 29DEC2017 1000

i USED ONE OF THE CODE GIVEN IN THIS COMMUNITY.

Data Want;

Informat id
st_dt
bal
open_dt
;
format st_dt date9.;
format open_dt date9.;

Retain prevST_TS .
prevBal
prevDOD
;

Set cohort_fd(Rename=(st_dt=xst_dt bal=xbal open_dt=xopen_dt));
By id
xst_dt
;

If NOT First.id Then
Do;
StartDate=prevST_TS;
If Last.id Then
Do;
StopDate=xst_dt;
End;
Else
Do;
StopDate=xst_dt-1;
End;

bal=prevBal;
open_dt=prevDOD;
Do st_dt=StartDate To StopDate;
Output;
End;

End;

prevST_TS=xst_dt;
prevBal=xbal;
prevDOD=xopen_dt;

format st_dt date9.;
format open_dt date9.;
format prevST_TS date9.;
format prevDOD date9.;
format xst_dt date9.;
format xopen_dt date9.;

Drop prevST_TS
prevBal
prevDOD
StartDate
StopDate
xst_dt
xbal
xopen_dt
;

Run;

output :

1. Issue is, if last.id date balance value is 100, instead of 100, it takes the previous value which was retained . 

2. I havent included the data to be retained till mar 2018 (which I dont know how to ) 

Please help on above 2 queries. 

Kindly help.

Thanks and Regards

Sandhya S

[Kurt_Bremser · Accepted solution]

Try this:

%let maxdate=%sysfunc(inputn(31mar2018,date9.));

data want;
merge
  bal_det
  bal_det ( /* "look ahead" into the next row */
    firstobs=2
    keep=st_dt id
    rename=(id=newid st_dt=newdate)
  )
;
output;
if id = newid
then do st_dt = st_dt + 1 to newdate - 1;
  output;
end;
else do st_dt = st_dt + 1 to &maxdate;
  output;
end;
drop newid newdate;
run;

[Sandy10]

Hi

Doesnt Merge take time to process? Becausr dataset will be containing
around 60K records. Kindly advise.

Thanks
Sandhya S

[Kurt_Bremser]

Since this "merge" merges the dataset with itself, just one observation offset, it is physically one sequential read (the second read of a dataset page happens from the cache), so no problem. And 60K obs (unless you have a monstrous observation size) are processed in less than a few seconds on any halfway current system.

If you experience problems, we need to know more about the dataset in question (eg a proc contents output).

If you have to create a LOT of new observations because of the way the data has gaps, then the write of the output will be your determining factor, no way around that.

[Sandy10]

Hi Kurtbesmer

Thanks for your reply. I will try the solution


Regards
Sandhya S