LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (November 2011, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 4 Nov 2011 07:13:14 -0400
Reply-To:     Rick Wicklin <Rick.Wicklin@SAS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Rick Wicklin <Rick.Wicklin@SAS.COM>
Subject:      Reshape data so that each category becomes a new variable

This is a question about efficient ways to reshape data from a "long" form to a "wide" form. I have a categorical variable C with categories C1, C2,...,Ck, and a continuous variable X. I want new variables X_C1, X_C2, ..., X_Ck where each variable contains the data in a subset of X given by WHERE=(C=C1) through WHERE=(C=Ck).

Example:Sashelp.Class contains the SEX variable (C) and the X variable (HEIGHT). I want new variables X_F and X_M.

A complete formulation of this problem, two answers I came up with, and example data are one my blog: http://bit.ly/u8p7qf (I also know a way to solve this problem by using PROC TRANSPOSE twice).

The rules are that you can't "cheat" by knowing how many categories there are (k) or their values (C1, C2, ..., Ck). These have to be discovered by using, say, PROC FREQ or PROC SQL.

Can anyone come up with a better solution than the ones I posted? The second solution (which uses SQL and the DATA step), does not seem very efficient because it makes k passes through the data. Extra points earned for short and readable solutions.

Thanks, Rick Wicklin SAS/IML blog: http://blogs.sas.com/content/iml


Back to: Top of message | Previous page | Main SAS-L page