SAS SQL Macro for concatenating or pivoting vertical time series horizontally

This post presents a SAS macro for concatenating or pivoting vertical time series horizontally. This job is done typically by repeatedly left joining the table on itself. Since this direct approach needs too long SQL command to maintain, one line SAS SQL macro is a good choice.

SAS SQL Macro for pivoting horizontally

Data

As an exercise, we use sashlep.stocks dataset in built-in example dataset in SAS.

To prepare dataset for a proper experiment, we aggregate daily stock prices to monthly averages and transform date for year-month to string. In practice yyyymm is used as a string not a date so that single quotes are used like '202001'.

Repeatedly Left Joining for Pivoting

We can use the following SAS SQL to create new columns for representing time series horizontally by left joining repeatedly.

Running the above SQL delivers the following output which shows each time series horizontally by stock names.

But this SQL has a problem. As the number of date(yyyymm) increases, the length of SAS SQL code will be approximately two times the number of date(yyyymm). For example, when a sample period ranges from 200101 to 202012, SQL code will be of more than 2*12*20 = 480 lines.

SAS Macro

The SAS macro below does the same job as we did by using left joining repeatedly. You can easily learn SAS macro technique by comparing direct left joining SQL and this macro.

This macro code has three arguments : nt(new table name), sm(start yyyymm), em(end yyyymm). Now that we can change start and end date, we can get the following long results by using only one line SAS macro without any long and lengthy SQL command.

Concluding Remarks

In this post, we implements SAS SQL macro for concatenating or pivoting vertical time series horizontally. This SAS macro will reduce our space and save our time for data analysis. \(\blacksquare\)