Date: Mon, 13 Sep 2010 06:19:45 -0400
Reply-To: Nat Wooding <nathani@VERIZON.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Nat Wooding <nathani@VERIZON.NET>
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.
