topic Re: Propagate a row untill next date value and continue till certain date in SAS Programming

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

Sandy10 — Wed, 18 Apr 2018 11:22:40 GMT

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

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

Kurt_Bremser — Wed, 18 Apr 2018 11:43:38 GMT

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;

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

Sandy10 — Wed, 18 Apr 2018 14:03:37 GMT

Hi

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

Thanks
Sandhya S

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

Kurt_Bremser — Wed, 18 Apr 2018 14:33:30 GMT

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.

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

Sandy10 — Wed, 18 Apr 2018 14:48:37 GMT

Hi Kurtbesmer

Thanks for your reply. I will try the solution

Regards
Sandhya S