johncohen.pdf - PharmaSUG 2015 Paper TT12 PROC TRANSPOSE For Fun And Profit John J Cohen Advanced Data Concepts LLC Newark DE Abstract Occasionally we

johncohen.pdf - PharmaSUG 2015 Paper TT12 PROC TRANSPOSE...

This preview shows page 1 - 3 out of 18 pages.

1 PharmaSUG 2015 - Paper TT12 PROC TRANSPOSE ® For Fun And Profit John J. Cohen, Advanced Data Concepts, LLC, Newark, DE Abstract Occasionally we are called upon to transform data from one format into a “flipped,” sort of mirror image. Namely if the data were organized in rows and columns, we need to transpose these same data to be arranged instead in columns and rows. A perfectly reasonable view of incoming lab data, ATM transactions, or web “click” streams may look “wrong” to us. A lternatively extracts from external databases and production systems may need massaging prior to proceeding in SAS ® . Finally, certain SAS procedures may require a precise data structure, there may be particular requirements for data visualization and graphing (such as date or time being organized horizontally/along the row rather than values in a date/time variable), or the end user/customer may have specific deliverable requirements. Traditionalists prefer using the DATA step and combinations of Array, Retain, and Output statements. This approach works well but for simple applications may require more effort than is necessary. For folks who intend to do much of the project work in, say, MS/Excel ® , the resident transpose option when pasting data is a handy short cut. However, if we want a simple, reliable method in SAS which once understood will require little on-going validation with each new run, then PROC TRANSPOSE is a worthy candidate. We will step through a series of examples, elucidating some of the internal logic of this procedure and its options. We will also touch on some of the issues which cause folks to shy away and rely on other approaches. Introduction We will take a simple dataset and transpose it at the simplest level using a standard Proc Sort/Data step approach. We will demonstrate the same using Excel transpose. And finally we will use Proc Transpose to transform the same data. We will then explore Proc Transpose in increasing complexity as we test some of the many options. We will demonstrate certain errors and test strategies for overcoming these. When completed, we expect that you will also agree that Proc Transpose has value as another tool in your SAS tool kit. The dataset we will use for our examples consist of a modified/simplified version of a standard sample dataset provided with most SAS installations, sashelp.retail. It will look substantially similar to Figure 1: Figure 1 sashelp.retail dataset
Image of page 1