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 (December 2008, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 30 Dec 2008 21:03:11 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: SQL first-like solution

On Tue, 30 Dec 2008 16:58:24 -0600, Joe Matise <snoopy369@GMAIL.COM> wrote:

>Why left join as opposed to full outer join (or, right join) ? Won't that >delete any records that don't have a 1992 value at all (as opposed to >leaving a missing value for the 1992 value variable)?

Since the right source (alias NINETY2) is derived from the table serving as the left source (HAVE), it's not possible for an ID to be represented in the right source but not the left.

> >-Joe > >On Tue, Dec 30, 2008 at 4:50 PM, Howard Schreier <hs AT dc-sug DOT org> < >schreier.junk.mail@gmail.com> wrote: > >> On Tue, 30 Dec 2008 16:38:24 -0600, Joe Matise <snoopy369@GMAIL.COM> >> wrote: >> >> >My SQL is not that great, but this should work: >> > >> > >> > >> >proc sql >> >noprint; >> > >> >create table want >> >as >> > >> >select id,year,value1,value2 >> >from >> > >> >( >> > >> >select id,year,value1 from have) >> >A >> > >> >full outer >> >join >> > >> >(select id as bid,value1 as value2 from have where year=1992) >> >B >> >on >> >id=bid; >> > >> >quit; >> > >> > >> >it's not actually doing 'first' (as you don't actually want first, after >> >all) ... it's not super efficient or anything, as it's doing two full >> table >> >access searches, but it should work. (I use 'bid' instead of 'id' in the >> >second table because I can never get around the ambiguous reference >> problem, >> >even using A.id and B.id ...) >> > >> >-Joe >> >> A variation: >> >> proc sql; >> create table need as >> select have.*, value2 >> from (select id, value1 as Value2 >> from have >> where year eq 1992) as ninety2 >> left join >> have >> on have.id eq ninety2.id; >> quit; >> >> It could be abridged a bit more as a NATURAL join. >> >> > >> >On Tue, Dec 30, 2008 at 4:14 PM, Arthur Tabachneck <art297@netscape.net >> >wrote: >> > >> >> Is there a sql solution for the problem raised earlier today, but with >> the >> >> modification that one wants to apply a particular value to all of the >> >> records within a group? >> >> >> >> That is, given: >> >> >> >> data have; >> >> input ID YEAR Value1; >> >> cards; >> >> 1 1991 20 >> >> 1 1992 1 >> >> 1 1993 30 >> >> 1 1994 23 >> >> 2 1992 2 >> >> 2 1993 4 >> >> 2 1994 51 >> >> ; >> >> >> >> how could one use sql to produce the following data set where the >> desired >> >> value for Value2 is Value1 when year (within the particular ID) is equal >> to >> >> 1992? >> >> >> >> data want; >> >> input ID YEAR Value1 Value2; >> >> cards; >> >> 1 1991 20 1 >> >> 1 1992 1 1 >> >> 1 1993 30 1 >> >> 1 1994 23 1 >> >> 2 1992 2 2 >> >> 2 1993 4 2 >> >> 2 1994 51 2 >> >> ; >> >> >> >> Just wondering. >> >> >> >> Art >> >> >>


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