Date:         Tue, 28 Nov 2006 22:41:51 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Comparing values within a one to many dataset
Comments: To: swovcc@hotmail.com
In-Reply-To:  <200611282235.kASJjrnp012312@malibu.cc.uga.edu>
Content-Type: text/plain; format=flowed

Yeah but then again what if it is already sorted, we could if this sucker to
death and still get no here.  If it is already sorted by Id and Type then
SQL will yeild the same results.  If however the file isnt already sorted by
Id and Type then it aint gonna yeild the same results.  I guess as always it
comes down to know your data and its structure.



Toby Dunn

Quickly, bring me a beaker of wine, so that I may wet my mind and say
something clever.
Aristophanes

Wise people, even though all laws were abolished, would still lead the same
life.
Aristophanes

You should not decide until you have heard what both have to say.
Aristophanes






From: Venky Chakravarthy <swovcc@HOTMAIL.COM>
Reply-To: Venky Chakravarthy <swovcc@HOTMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Comparing values within a one to many dataset
Date: Tue, 28 Nov 2006 17:35:44 -0500

Toby,

There is potential for something to go wrong even with this type of
example. What if I make the following small changes to the input data. Your
SQL solution will have to undergo some modification to yield correct
results.

Data type;
input id type exam;
datalines;
1 1 1
1 2 2
1 1 3
1 2 4
1 2 5
2 2 1
2 1 2
2 2 3
2 2 4
3 2 1
3 1 2
3 1 3
4 2 1
4 2 2
4 2 3
4 1 4
4 1 5
5 1 1
5 1 2
5 1 3
5 1 4
;
run;

On Tue, 28 Nov 2006 22:22:00 +0000, toby dunn <tobydunn@HOTMAIL.COM> wrote:

 >Yeah row order seems kind a bad thing ussually with SQL, but when your
 >messing with a sort order as the example it doesnt matter.
 >
 >
 >
 >Toby Dunn
 >
 >Quickly, bring me a beaker of wine, so that I may wet my mind and say
 >something clever.
 >Aristophanes
 >
 >Wise people, even though all laws were abolished, would still lead the
same
 >life.
 >Aristophanes
 >
 >You should not decide until you have heard what both have to say.
 >Aristophanes
 >
 >
 >
 >
 >
 >
 >From: Venky Chakravarthy <swovcc@HOTMAIL.COM>
 >Reply-To: Venky Chakravarthy <swovcc@HOTMAIL.COM>
 >To: SAS-L@LISTSERV.UGA.EDU
 >Subject: Re: Comparing values within a one to many dataset
 >Date: Tue, 28 Nov 2006 17:16:55 -0500
 >
 >On Tue, 28 Nov 2006 16:40:02 -0500, Jen <plessthanpointohfive@GMAIL.COM>
 >wrote:
 >
 > >Hi, all,
 > >
 > >I have a dataset such as below:
 > >
 > >Data type;
 > >input id type exam;
 > >datalines;
 > >1 1 1
 > >1 1 2
 > >1 1 3
 > >1 2 4
 > >1 2 5
 > >2 2 1
 > >2 2 2
 > >2 2 3
 > >2 2 4
 > >3 2 1
 > >3 1 2
 > >3 1 3
 > >4 2 1
 > >4 2 2
 > >4 2 3
 > >4 1 4
 > >4 1 5
 > >5 1 1
 > >5 1 2
 > >5 1 3
 > >5 1 4
 > >;
 > >run;
 > >
 > >What I want to do is compare the first and last value of Type within an
 >ID.
 > >If the first and last values for Type = 2 (such as in Id=2) then I want
to
 > >output into another dataset.  If the first and last values for Type = 1
 >then
 > >I want to label that entire ID as Subset=1.  If the first and last
values
 > >for Type are not equal to each other then I want to label that entire ID
 >as
 > >Subset=2.
 > >
 > >I'm not afraid of SQL so feel free to suggest it.
 >
 >In this case one would feel justified to feel scared of using SQL. What
you
 >are seeking is based on row order, something that SQL does not handle as
 >elegantly as the data step.
 >
 >data all2s rest ;
 >   do until (last.id) ;
 >      set type ;
 >      by id ;
 >      if first.id then first = type ;
 >      if last.id then last = type ;
 >   end ;
 >   subset = (first = last = 1) + 2*(first ^= last) + 3*(first = last = 2)
;
 >   do until (last.id) ;
 >      set type ;
 >      by id ;
 >      if subset = 3 then output all2s ;
 >      else output rest ;
 >   end ;
 >   drop first last ;
 >run ;
 >
 > >
 > >Thanks,
 > >
 > >Jen
 >
 >You are welcome.
 >
 >Venky Chakravarthy
 >
 >_________________________________________________________________
 >Fixing up the home? Live Search can help
 >http://imagine-windowslive.com/search/kits/default.aspx?
kit=improve&locale=en-US&source=hmemailtaglinenov06&FORM=WLMTAG

_________________________________________________________________
Get free, personalized commercial-free online radio with MSN Radio powered
by Pandora http://radio.msn.com/?icid=T002MSN03A07001