Date:         Tue, 30 Dec 2008 18:30:29 -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
Comments: To: Joe Matise <snoopy369@GMAIL.COM>

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
>> >>
>>