BookmarkSubscribeRSS Feed

BASUG: History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies

BASUG logo.png

Many programming tasks require merging time series of varying frequency. For instance you might have three datasets (YEAR, QTR, and MONTH) of data, each with eponymous frequency and sorted by common id and date variables. Producing a monthly file with the most recent quarterly and yearly data is a hierarchical last-observation-carried-forward (LOCF) task. Or you may have three irregular times series (ADMISSIONS, SERVICES, TESTRESULTS), in which you want to capture the latest data from each source at every date encountered (event-based LOCF). These are tasks often left poorly optimized by most SQL-based languages, in which row order is ignored in the interests of optimizing table manipulation.


This presentation shows how to use conditional SET statements in the SAS® DATA step to update specific portions of the program data vector (i.e. the YEAR variables or the QTR variables) to carry forward low frequency data to multiple subsequent high frequency records. A similar approach works just as well for carrying forward data from irregular time series. We’ll also show how to use “sentinel variables” as a means of controlling the maximum time-span data is carried forward, i.e. how to remove historical data that has become “stale.” Finally, we will demonstrate how to modify these techniques to carry future observations backward, without re-sorting data.


Presented by Mark Keintz

Community Manager
Event has ended
You can no longer attend this event.

Wed, May 8, 2024 12:00 PM EDT
Wed, May 8, 2024 01:00 PM EDT