32288 - How to collapse multiple rows of data into a single row within a group

Sample 32288: How to collapse multiple rows of data into a single row within a group

Overview

Many times you have data that contains multiple rows and multiple columns of significant information within a group. You would like to collapse the data for each group into a single row of data. This can be done within SAS® Enterprise Guide by using a series of code nodes along with the Transpose task and Split Columns task.

Below is some sample data in the original layout. In this data, there are two columns that need to be collapsed into a single row per group.

image label

To download this data, click the Downloads tab at the top of this sample.

Because of the way the data is structured, you must make sure that there is a unique identifier within each group. Use the unique identifier along with the group variable to transpose the data into two columns, one with the names that you want to use for the columns in the collapsed data, and one with all of the values that are associated with each variable. Next, use another code node to create unique variable names within each group. If the variable names are not unique, the Split Columns task will return an error. After the unique names are created in the code window, use the Split Columns task to collapse the data into a single row per group.

Here is a what the final collapsed data set will look like:

image label

Collapse the Data

To collapse a table with multiple rows and multiple columns within a group to a single row per group, follow these steps.

Open the sample data using File ► Open ► Data.
Insert a new Code window by selecting File ► New ► Code, and then use the following code in order to assign the unique identifier within the By group.

image label

Select Tasks ► Data ► Transpose and specify all of the variables that you want to collapse as the Transpose variables. Also, specify the group variable and N as the Group analysis by variables. If you want to modify the output table name, you can do so on the Results window in the task.

image label

Insert a second new Code window to create the unique column names for the variables. Use File ► New ► Code to create the Code window, and type code similar to the following:

image label

Run the Split column task to collapse the data for each group into a single row of data. Select Tasks ► Data ► Split Columns to open the task. For the Task roles, specify COLUMN1 as your Column to split, NEWNAME as the Value identifier column, and group variable as your Group analysis by column. If you want to modify the output table, you can do so on the Results window in the task.

image label

The data should now be collapsed into a single row per group.

image label

Additional Documentation

For a list of available documentation for SAS Enterprise Guide, see http://support.sas.com/documentation/onlinedoc/guide/

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Date Modified:	2008-06-17 16:45:45
Date Created:	2008-05-30 16:33:31

Product Family	Product	Host	Product Release		SAS Release
Product Family	Product	Host	Starting	Ending	Starting	Ending
SAS System	SAS Enterprise Guide	Microsoft® Windows® for x64	4.1
		Microsoft Windows 95/98	4.1
		Microsoft Windows 2000 Advanced Server	4.1
		Microsoft Windows 2000 Datacenter Server	4.1
		Microsoft Windows 2000 Server	4.1
		Microsoft Windows 2000 Professional	4.1
		Microsoft Windows NT Workstation	4.1
		Microsoft Windows Server 2003 Datacenter Edition	4.1
		Microsoft Windows Server 2003 Enterprise Edition	4.1
		Microsoft Windows Server 2003 Standard Edition	4.1
		Microsoft Windows XP Professional	4.1
		Windows Millennium Edition (Me)	4.1
		Windows Vista	4.1

Support

Sample 32288: How to collapse multiple rows of data into a single row within a group

Overview

Collapse the Data

Additional Documentation

Operating System and Release Information