Date: Tue, 30 Dec 2008 18:50:51 -0500
Reply-To: Arthur Tabachneck <art297@NETSCAPE.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Arthur Tabachneck <art297@NETSCAPE.NET>
Subject: Re: SQL first-like solution
Joe,
What I can add is that where an ID is missing a value for 1992, Howard's
suggested code doesn't include the record(s), while your suggested code
does, but sets Value2 to missing.
For what I was envisioning, both solutions would suffice as long as one
knew what to expect.
Art
------
On Tue, 30 Dec 2008 18:30:29 -0500, Arthur Tabachneck
<art297@NETSCAPE.NET> wrote:
>Joe,
>
>I can't answer your question, but many thanks to both you and Howard for
>the solutions. They both appear to provide exactly what I was looking
for.
>
>Art
>-------
>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)?
>>
>>-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
| 
