DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

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

[ Edited ]

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

 

 

 

 


Accepted Solutions
Solution
‎04-24-2018 04:06 AM
Super User
Posts: 9,888

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎04-24-2018 04:06 AM
Super User
Posts: 9,888

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 39

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

Posted in reply to KurtBremser
Hi

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

Thanks
Sandhya S
Super User
Posts: 9,888

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 39

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

Posted in reply to KurtBremser
Hi Kurtbesmer

Thanks for your reply. I will try the solution


Regards
Sandhya S
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 89 views
  • 0 likes
  • 2 in conversation