LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2007, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 18 Apr 2007 10:02:12 -0500
Reply-To:     "Huang, JS" <Huang.JS@PRINCIPAL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, JS" <Huang.JS@PRINCIPAL.COM>
Subject:      Re: Need help from a data manipulation expert
Comments: To: andymanme@GOOGLEMAIL.COM
In-Reply-To:  A<1176902974.995552.18640@b75g2000hsg.googlegroups.com>
Content-Type: text/plain; charset="windows-1255"

Here is an implementation:

options nomprint nomlogic nosymbolgen; data inputfile; input ID Name $ Position; datalines; 111 Jane 1 111 Sophie 2 111 James 3 111 Adrian 4 111 Tim 5 222 James 1 222 Julian 2 222 Tim 3 222 Chris 4 333 Jane 1 333 Sophie 2 333 Darren 3 333 Adrian 4 ; run;

proc sql noprint; create table VariableName as select distinct trim(left(Name)) as Name from inputfile order by Name; %let VarCount=&sqlobs; select distinct trim(left(Name))into: NewVarName separated by ' ' from VariableName; select distinct "'" || trim(left(Name)) || "'" into: NewName separated by ' ' from VariableName; select trim(left(Name)) into: NameComma separated by ', ' from VariableName; drop table VariableName; create table Combined as select a.Name as First, b.Name as Second from inputfile a, inputfile b where a.ID eq b.ID and a.Position lt b.Position; quit;

%put VarCount=&VarCount; %put NewVarName=&NewVarName; %put NewName=&NewName;

data Temp(drop=i First Second); array NewVariable (&VarCount) &NewVarName; array NewVarName (&VarCount)$ _temporary_ (&NewName); set Combined; do i=1 to dim(NewVariable); if First=NewVarName(i) then NewVariable(i)=1; if Second=NewVarName(i) then NewVariable(i)=-1; end; do i=1 to dim(NewVariable); if NewVariable(i)=. then NewVariable(i)=0; end; run;

proc sql; select &NameComma, count(*) as Count from Temp group by &NameComma; quit;

***** Output ***** The SAS System 08:13 Wednesday, April 18, 2007 38

Adrian Chris Darren James Jane Julian Sophie Tim Count ャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャ -1 0 0 0 0 0 1 0 2 -1 0 0 0 1 0 0 0 2 -1 0 0 1 0 0 0 0 1 -1 0 1 0 0 0 0 0 1 0 -1 0 0 0 0 0 1 1 0 -1 0 0 0 1 0 0 1 0 -1 0 1 0 0 0 0 1 0 0 -1 0 0 0 1 0 1 0 0 -1 0 1 0 0 0 1 0 0 0 -1 0 0 1 0 1 0 0 0 -1 1 0 0 0 1 0 0 0 0 0 0 1 -1 1 0 0 0 0 0 1 0 -1 1 0 0 0 0 1 0 -1 0 2 0 0 0 0 1 0 0 -1 1 0 0 0 1 0 -1 0 0 1 0 0 0 1 0 0 0 -1 2 1 0 0 0 0 0 0 -1 1

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of andymanme@GOOGLEMAIL.COM Sent: Wednesday, April 18, 2007 8:30 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Need help from a data manipulation expert

Hi everyone,

Hope someone can help. I am trying to take a large file of data I have on our local club running races and manipulate this to be in a specific form so that I can build a Bradley Terry model.

My starting point is the data I already have, which is of the form:

data inputfile; input ID Name $ Position; datalines; 111 Jane 1 111 Sophie 2 111 James 3 111 Adrian 4 111 Tim 5 222 James 1 222 Julian 2 222 Tim 3 222 Chris 4 333 Jane 1 333 Sophie 2 333 Darren 3 333 Adrian 4 ; run;

Where ID identifies a particular race, Name is the persons name running in the race and Position is the final finishing position at the end of the race.

What I want to do is to transform this data into something which looks like:

data outputfile; input Jane Sophie James Adrian Tim Julian Chris Darren Count; datalines; 1 -1 0 0 0 0 0 0 2 1 0 -1 0 0 0 0 0 1 1 0 0 -1 0 0 0 0 2 1 0 0 0 -1 0 0 0 1 1 0 0 0 0 0 0 -1 1 0 1 -1 0 0 0 0 0 1 0 1 0 -1 0 0 0 0 2 0 1 0 0 -1 0 0 0 1 0 0 1 -1 0 0 0 0 1 0 0 1 0 -1 0 0 0 2 0 0 1 0 0 -1 0 0 1 0 0 1 0 0 0 -1 0 1 ; /* I won't produce the _whole_ table here */ run;

Hopefully you get the idea, for example Jane has beaten Sophie 2 times, and Sophie has beaten James once (by beating him in the same race when she lost to Jane). Sophie has also beaten Adrian twice (in races 111 and 333) etc.

So I need some code which will cycle through each race counting the number of times each person has beaten each other person (the count variable), with the winner being given a 1 in their variable name and a loss is -1 in their variable name, the count variable then counting the instances of this happening.

I hope someone can help! Andy

-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended recipient, any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by reply email to Connect@principal.com and delete or destroy all copies of the original message and attachments thereto. Email sent to or from the Principal Financial Group or any of its member companies may be retained as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature for purposes of the Uniform Electronic Transactions Act (UETA) or the Electronic Signatures in Global and National Commerce Act ("E-Sign") unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction or an idea that is discussed in the publication, it is intended to provide general information about the subject matter covered and is provided with the understanding that The Principal is not rendering legal, accounting, or tax advice. It is not a marketed opinion and may not be used to avoid penalties under the Internal Revenue Code. You should consult with appropriate counsel or other advisors on all matters pertaining to legal, tax, or accounting obligations and requirements.


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