Date:         Fri, 25 May 2007 08:59:53 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: how do I do a merge like this?

On Fri, 25 May 2007 02:23:35 -0700, Daniel Nordlund <res90sx5@VERIZON.NET>
wrote:

>> -----Original Message-----
>> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>> RolandRB
>> Sent: Friday, May 25, 2007 2:00 AM
>> To: SAS-L@LISTSERV.UGA.EDU
>> Subject: how do I do a merge like this?
>>
>> How do I do a merge like this? I guess it can be done with SQL but I
>> am useless at SQL.
>>
>> id pat
>> 1  001
>> 1  002
>> 2  001
>> 3  001
>> 3  002
>> 4  001
>> 4  002
>> 5  001
>>
>> id newid
>> 1  new11
>> 1  new12
>> 3  new31
>> 3  new32
>>
>>
>> RESULT
>> id pat newid
>> 1  001 new11
>> 1  001 new12
>> 1  002 new11
>> 1  002 new12
>> 2  001
>> 3  001 new31
>> 3  001 new32
>> 3  002 new31
>> 3  002 new32
>> 4  001
>> 4  002
>> 5  001
>
>Roland,
>
>How about something like
>
>data have1;
>  input id pat &;
>cards;
>1  001
>1  002
>2  001
>3  001
>3  002
>4  001
>4  002
>5  001
>;
>run;
>data have2;
>  input id newid $;
>cards;
>1  new11
>1  new12
>3  new31
>3  new32
>;
>run;
>
>proc sql;
>create table want as
>  select a.*, b.newid
>    from have1 as a left join have2 as b
>     on a.id=b.id
>   ;
>quit;
>proc print data=want;
>run;
>
>Hope this is helpful,
>
>Dan
>
>Daniel Nordlund
>Bothell, WA USA

Since there are no common variables other than the key, it can be just

   select * from have2 natural right join have1;