Automate Dummy Variable Creating from Large Longitudinal RDBMS Tables --- A Pedagogical Example of the SAS/Connect ® Piping Facility

Given a list of patient demographic characteristics and the patient lab records in RDBMS tables separated by years, we can create a list of summary variables per specification per person almost automatically using 3 parallel processes: process #1 selects all lab records only for the patients in our ID list and writes them into pipe #1; process #2 reads records from pipe #1, cleans lab results into appropriate numeric values and writes them into pipe #2; process #3 reads lab values from pipe #2 and aggregates them into person-level characteristics. Tricks of SAS/Access for OLEDB, regular expression, Hash object, and macro programming are illustrated too. We also discuss the advantages and limitations of our methods. This paper fills in the gap of the lack of a working example on the piping facility.