```Date: Mon, 13 Sep 2010 06:19:45 -0400 Reply-To: Nat Wooding Sender: "SAS(r) Discussion" From: Nat Wooding Subject: Re: Data Transformation In-Reply-To: <201009130648.o8D4EUar018709@willow.cc.uga.edu> Content-Type: text/plain; charset="US-ASCII" Paul I simplified the data a little and made some changes to the input data step. The code below appears to do what you are attempting. You will need to be using V9 for the CATX function to be available. Nat Wooding Data wide; INFILE DATALINES4 DLM=',' MISSOVER DSD ; informat expected_interaction \$2. a \$3. b \$3. c \$3. d \$3. e \$3.; INPUT expected_interaction a b c d e ; cards; A , , YES, , , YES C,YES, YES , , YES run; proc transpose data = wide out = narrow ( where = ( Col1 =:'YES')); var _character_; by expected_interaction ; run; Data Final; set narrow ; Cause = catx( ' ' , 'Product' , Expected_Interaction ); Impact = catx( ' ' , 'Product' , upcase( _name_ )); drop exp: col1 _name_ ; run; -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Paul Sent: Monday, September 13, 2010 2:49 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Data Transformation Hello SAS-Lers As part of my data cleaning routine I would like to convert a matrix in the SAS data set below. DATA Expected_Interaction; INFORMAT expected_interaction \$CHAR2. a \$CHAR3. b \$CHAR3. c \$CHAR3. d \$CHAR3. e \$CHAR3. f \$CHAR3. g \$CHAR3. h \$CHAR3. i \$CHAR3. j \$CHAR3. k \$CHAR3. l \$CHAR3. m \$CHAR3. n \$CHAR3. o \$CHAR3. p \$CHAR3. q \$CHAR3. r \$CHAR3. s \$CHAR3. t \$CHAR3. u \$CHAR3. v \$CHAR3. w \$CHAR3. x \$CHAR3. y \$CHAR3. z \$CHAR3. aa \$CHAR3. ab \$CHAR3. ac \$CHAR3. ad \$CHAR3. ae \$CHAR3. af \$CHAR3. ag \$CHAR3. ah \$CHAR3. aj \$CHAR3. ak \$CHAR3. al \$CHAR3. am \$CHAR3. an \$CHAR3. ao \$CHAR3. ; INFILE DATALINES4 DLM='7F'x MISSOVER DSD ; INPUT expected_interaction : \$CHAR2. a : \$CHAR3. b : \$CHAR3. c : \$CHAR3. d : \$CHAR3. e : \$CHAR3. f : \$CHAR3. g : \$CHAR3. h : \$CHAR3. i : \$CHAR3. j : \$CHAR3. k : \$CHAR3. l : \$CHAR3. m : \$CHAR3. n : \$CHAR3. o : \$CHAR3. p : \$CHAR3. q : \$CHAR3. r : \$CHAR3. s : \$CHAR3. t : \$CHAR3. u : \$CHAR3. v : \$CHAR3. w : \$CHAR3. x : \$CHAR3. y : \$CHAR3. z : \$CHAR3. aa : \$CHAR3. ab : \$CHAR3. ac : \$CHAR3. ad : \$CHAR3. ae : \$CHAR3. af : \$CHAR3. ag : \$CHAR3. ah : \$CHAR3. aj : \$CHAR3. ak : \$CHAR3. al : \$CHAR3. am : \$CHAR3. an : \$CHAR3. ao : \$CHAR3. ; DATALINES4; A YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES B YES YES YES YES YES YES YES YES YES YES C YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES D YES YES YES E YES F YES YES YES YES YES YES YES G YES YES YES H YES YES YES YES YES YES YES I YES YES YES YES YES YES YES YES YES YES YES YES J YES YES YES YES YES YES YES YES YES YES YES YES K YES YES YES YES YES YES YES YES YES L YES YES YES YES YES YES YES YES YES M YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES N YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES O YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES P YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES Q YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES R YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES S YES YES YES YES YES YES YES YES YES YES T YES YES YES YES YES YES YES YES YES YES YES YES YES YES U YES YES YES YES YES YES YES YES YES YES YES YES YES YES V YES YES YES YES YES YES YES YES YES YES YES YES YES YES W YES YES YES X YES YES YES Y YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES Z YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AA YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AB YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AC YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AD AE YES AF YES YES YES YES AG YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AH YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AJ YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AK YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AL YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AM YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES AN YES YES YES YES YES YES YES AO YES YES YES YES YES YES YES YES YES YES ;;;; run; something a data set with only 2 columns Cause Impact product A product A product A product B product A product D . . . . . . product A product Z product B product A product B product B product B product D . . . . . . product B product Z product C product A product C product B product C product D . . . . . . product C product Z Any suggestions to speed up this part of data cleaning would be much appreciated. ```

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