| Date: | Wed, 13 Aug 2008 09:01:43 -0500 |
|---|
| Reply-To: | "./ ADD NAME=Data _null_," <iebupdte@GMAIL.COM> |
|---|
| Sender: | "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> |
|---|
| From: | "./ ADD NAME=Data _null_," <iebupdte@GMAIL.COM> |
|---|
| Subject: | Re: full join |
|---|
|
| In-Reply-To: | <6716d5d0808130631n44fd56cela5c8e96e0cab33a4@mail.gmail.com> |
|---|
| Content-Type: | text/plain; charset=ISO-8859-1 |
|---|
maybe this,
coalesce(dept.dept_id,staff.dept_id) as ID_DEPT
On 8/13/08, Jeff <zhujp98@gmail.com> wrote:
> data dept ;
> length dept $10 ;
> input dept_id
> dept $
> ;
> cards ;
> 10 Household
> 50 Furniture
> 20 Gardening
> 30 Toys
> 40 Sport
> ;
> run ;
>
> data staff ;
> length surname $10 ;
> input dept_id
> surname $
> ;
> cards ;
> 10 Cleal
> 30 Steiff
> 20 Rudland
> 20 Norrie
> 40 Smith
> 10 Pazikas
> 35 Houghton
> ;
> run ;
>
> proc sql ;
> create table fulljoin as
> select *
> from work.dept full outer join
> work.staff
> on dept.dept_id = staff.dept_id
> ;
> quit ;
> run ;
>
> will generate results as follows:
> dept dept_id surname
>
> Household 10 Cleal
> Household 10 Pazikas
> Gardening 20 Rudland
> Gardening 20 Norrie
> Toys 30 Steiff
> . Haughton
> Sport 40 Smith
> Furniture 50
>
> The problem is the id 35 from second table is missing in the result table.
> Is there a way for me to let 35 stay in the final result?
> Thanks.
> Jeff
>
|
