Date:         Mon, 5 Oct 2009 09:35:24 -0700
Reply-To:     "Richard A. DeVenezia" <rdevenezia@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: Best way to rearrange dyadic data
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Oct 2, 9:48 am, peterflomconsult...@mindspring.com (Peter Flom)
wrote:
> Good morning all
>
> It's been a while since I've asked a question :-)
>
> Before posting this one, I searched SAS-L and found similar questions (including some by me!) but I also found advice that each case is a little different.... so...
>
> I have a data set with data on heterosexual couples.  Currently, each line is an indvidual, and there are ~1000 variables.  Each line includes
>
> COUPLE - which is the couple ID
> ID - which is the individual's ID
> SEX - which is the individual's sex
> many many other variables, e.g. marital status.  So, it looks something like this
> ---------------------------------------
> DATA HAVE
> Couple       ID       Sex       A11
> 10260        10261    Male      Single, Never Married
> 10260        10262    Female    Widowed
> 10380        10381    Male      Married
> 10380        10382    Female    Married
> 10280        10281    Male      Married
> 10280        10282    Female    Married

For data integrity reasons, you may want to have two tables

PERSONS
  PersonId, PersonAttribute1 .. PersonAttributeN

and
HETERO_COUPLES
  CoupleId, MalePersonId, FemalePersonId, HeteroCoupleAttribute1, ...
HeteroCoupleAttributeN

For long term data systems, a person attribute such as birthdate will
not change over the lifetime of the person, so that can go in the
PERSONS table.  However, things like addresses are apt to change, so
you may think about 'asof' attribute tables to factor in time variant
attributes
PERSON_PREMISES
  PersonId, AsOfDate, Street, City, Zip

These tables would be indexed and appropriately foreign keyed.  Views
would be used to join the base data into layout constructs needed to
perform your analytics.

You might also want to consider the coupling data in a transactional
way and have fields DATE and EVENT to record when certain milestones
occur MET, ENGAGE, DIVORCE, etc...

--
Richard A. DeVenezia