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.
SAS/IML blog: http://blogs.sas.com/content/iml