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