**Date:** Thu, 21 Oct 2010 22:06:16 -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: Need Help Creating a Matrix
**In-Reply-To:** <AANLkTi=iP6wc2nL5rZ2jXpzKb11m4erAp=NRX-z5cpPs@mail.gmail.com>
**Content-Type:** text/plain; charset="us-ascii"
It's late enough at night that I should probably forego answering posts but
I do note the line from the log:

NOTE: Variable ApproachedYouAdvice is uninitialized.

Which suggests to me that you have misspelled a variable name.

Nat Wooding

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Chao
Yawo
Sent: Thursday, October 21, 2010 9:57 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Need Help Creating a Matrix

Richard thanks very much. I run the code on a different set of data, with
the same structure. Though it produced the matrix, all the cells are filled
with zeros, even though the original data did have some 1s. my program
statements and the log is produced below and I would be very grateful if
you could help me out with some pointers. THanks - CY

=========
data ApproachAdvice;
set Emmasas.Cwn_only_tiedata_string;
run;

proc sort data=ApproachAdvice;
By RespID;
run;

proc sql;
select distinct RespID into :colnm separated by ' '
from ApproachAdvice;
data ApproachAdvice1;
array col &colnm;
do until(last.RespID);
set ApproachAdvice;
by RespID;
do over col;
if TieID=vname(col) and ApproachYouAdvice=1 then col=1;
end;
do over col;
if col=. then col=0;
end;
end;
run;

proc print;
var RespID &colnm;
run;

=========
147 data ApproachAdvice;
148 set Emmasas.Cwn_only_tiedata_string;
149 run;

NOTE: There were 1132 observations read from the data set
EMMASAS.CWN_ONLY_TIEDATA_STRING.
NOTE: The data set WORK.APPROACHADVICE has 1132 observations and 12
variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

150 proc sort data=ApproachAdvice;
151 By RespID;
152 run;

NOTE: There were 1132 observations read from the data set
WORK.APPROACHADVICE.
NOTE: The data set WORK.APPROACHADVICE has 1132 observations and 12
variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

153 proc sql;
154 select distinct RespID into :colnm separated by ' '
155 from ApproachAdvice;
NOTE: PROCEDURE SQL used (Total process time):
real time 22.92 seconds
cpu time 1.00 seconds

156 data ApproachAdvice1;
157 array col &colnm;
158 do until(last.RespID);
159 set ApproachAdvice;
160 by RespID;
161 do over col;
162 if TieID=vname(col) and ApproachedYouAdvice=1 then col=1;
163 end;
164 do over col;
165 if col=. then col=0;
166 end;
167 end;
168 run;

NOTE: Variable ApproachedYouAdvice is uninitialized.
NOTE: There were 1132 observations read from the data set
WORK.APPROACHADVICE.
NOTE: The data set WORK.APPROACHADVICE1 has 92 observations and 105
variables.
NOTE: DATA statement used (Total process time):
real time 0.44 seconds
cpu time 0.34 seconds

169 proc sql;
170 select distinct RespID into :colnm separated by ' '
171 from ApproachAdvice;
NOTE: PROCEDURE SQL used (Total process time):
real time 13.83 seconds
cpu time 1.07 seconds

172 data ApproachAdvice1;
173 array col &colnm;
174 do until(last.RespID);
175 set ApproachAdvice;
176 by RespID;
177 do over col;
178 if TieID=vname(col) and ApproachYouAdvice=1 then col=1;
179 end;
180 do over col;
181 if col=. then col=0;
182 end;
183 end;
184 run;

NOTE: There were 1132 observations read from the data set
WORK.APPROACHADVICE.
NOTE: The data set WORK.APPROACHADVICE1 has 92 observations and 104
variables.
NOTE: DATA statement used (Total process time):
real time 0.43 seconds
cpu time 0.36 seconds
====================================

On Mon, Oct 26, 2009 at 11:35 AM, Richard A. DeVenezia <rdevenezia@gmail.com
> wrote:

> On Oct 26, 9:41 am, Yawo1...@YAHOO.COM (Chao Yawo) wrote:
> > Hi,
> >
> > I have a dataset with the following structure.
> >
> > ColumnA ColumnB ColumnC
> > ClassA OtherStud WorkWith
> >
> > AA DD 1
> > AA EE 0
> > AA BB 1
> > BB FF 1
> > BB GG 1
> > BB KK 0
> > CC AA 1
> > CC BB 1
> >
> > ColumnA refers to students in ClassroomA, and Column B refers to "Other
> > Students" in the school who could act as work and/or playmates.
> >
> > Note, ColumnB could also include some of the same students in
> > ColumnA(ClassA) who work with each other (For example, Students: AA
> > and BB).
> >
> > If a Student in Classroom A works with a student in ClassroomB, ColumnC
> > takes the value of 1, and 0(zero) if otherwise
> >
> > So, looking at ColumnA, we find that there are three students: AA, BB
> and
> > CC. Student_AA for instance interacts with three students <DD, EE, BB>
> but only
> > works with DD and BB. Similarly, Student_CC interacts with two students
> > <AA, and BB> and works and plays with each of them.
> >
> > Now, I am interested in constructing a matrix indicating which
> > students in ClassroomA work with each other. This means
> > that student_AA for instance works with student_BB, who is in his/her
> > classroom, hence the cell in their matrix will have a "1".
> >
> > Again, Student_CC in ClassroomA works with 2 other members of
> > his/her own class<ie, AA and BB>. So CC would have two "1s" in the
> matrix.
> >
> > The resulting matrix for the "Works With" relation (ColumnC) among the 3
> > students in ClassroomA would look like this.
> >
> > AA BB CC
> > AA 0 1 0
> > BB 0 0 0
> > CC 1 1 0
> >
> > This matrix is the output I am interested in, and need to produce for
> > the relationship specified in Column C.
> >
> > I would certainly appreciate some ideas to help me produce this.
> >
> > Thanks in advance for your assistance.
>
> Chao:
>
> This is a transposition (always known as pivot) transformation.
>
> You can use Proc TRANSPOSE or a DATA Step with an Array to create the
> output table you want, or if you looking for just reports, Proc
> TABULATE will work for you.
>
> You do have an slightly advanced requirement, that the column names of
> the output only match the student name values in column A.
>
>
> This sample code uses SQL to determine the student names that are to
> be used as columns, performs a transpose, and then does a small DATA
> step to re-arrange the columns and set missings to zero.
>
> ----------------------------
> data have;
> input ClassA $ OtherStudent $ WorksWith;
> datalines;
> AA DD 1
> AA EE 0
> AA BB 1
> BB FF 1
> BB GG 1
> BB KK 0
> CC AA 1
> CC BB 1
> run;
>
> proc sql noprint;
> select distinct ClassA into :STUDENT_IDS separated by ' '
> from have;
> quit;
>
> proc transpose data=have out=want(keep=&STUDENT_IDS.);
> by ClassA;
> id OtherStudent;
> var WorksWith;
> run;
>
> data want;
> length &STUDENT_IDS. 8;
> set want;
> array tozero &STUDENT_IDS.;
> do _n_ = 1 to dim (tozero);
> if missing(tozero(_n_)) then
> tozero(_n_) = 0;
> end;
> run;
>
> %symdel &STUDENT_IDS.;
> ----------------------------
>
> Richard A. DeVenezia
> http://www.devenezia.com
>