Date:         Wed, 7 Jun 2006 13:30:00 -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: SQL: mapping same input column to different output columns

It's quite common to include a table more than once in a join. Just use
aliases to distinguish, as in

   ... zodiac AS h_zodiac, zodiac AS w_zodiac ...

Another possibility is to use correlated subqueries to look up the signs.

On Wed, 7 Jun 2006 10:16:06 -0700, Paul <paulvonhippel@YAHOO.COM> wrote:

>Using PROC SQL, I would like to map different values of the same input
>column to different output columns. Here is a simplified example of the
>problem.
>
>Suppose I have 4 tables:
>COUPLES (1 column called COUPLE_ID)
>HUSBANDS (2 columns: COUPLE_ID, BIRTH_DATE)
>WIVES (2 columns: COUPLE_ID, BIRTH_DATE)
>ZODIAC (2 columns: BIRTH_DATE, ZODIAC_SIGN)
>
>I'd like to create an output table called COUPLE_ZODIAC, containing
>three columns called COUPLE_ID, HUSBAND_ZODIAC_SIGN, and
>WIFE_ZODIAC_SIGN. What is the best way to do this? The trick (for me)
>is that HUSBAND_ZODIAC_SIGN and WIFE_ZODIAC_SIGN come from the same
>column in the same table.
>
>Suggestions most welcome.
>
>Thanks!