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

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

Mark Keintz has been using SAS® since it was documented in one book. His interests are largely in development of applications for financial research and education, addressed in several presentations at SAS Global Forums and various regional SAS user groups. Mark's primary SAS expertise is in DATA step programming, hash programming techniques, efficient use of large data sets, and macro programming.

REGISTER for this virtual event today.