Date:         Wed, 29 May 2002 17:49:16 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: merge multiple observations into a single observation w/o ove
              rinf lating matches
Comments: To: "Dorfman, Paul" <Paul.Dorfman@BCBSFL.COM>
Content-Type: text/plain; charset="iso-8859-1"

Has anyone else noticed that a number of hard-core datastep programmers are
now crossing over and posting SQL solutions? First Peter Crawford, then
Hash-man .... Who next? Dr. John?

I have only one thing to say about these incursions into what I used to
consider my own little enclave. Welcome!

Sig

-----Original Message-----
From: Dorfman, Paul [mailto:Paul.Dorfman@BCBSFL.COM]
Sent: Wednesday, May 29, 2002 4:31 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: merge multiple observations into a single observation w/o
ove rinf lating matches


Shannon,

If you do not mind a shot from an SQL-impared (suppose your input data set
is called A):

Proc SQL ;
   Create table Match as
   Select distinct x.id
        , y.parttrig
        , x.partresp
        , x.evtype
        , x.actcode
        , x.finalact
   From A (where=(actcode=68)) x
          left join
        A (where=(actcode=64)) y
     on  x.id       = y.id
     and x.evtype   = y.evtype
     and x.partresp = y.parttrig
   Order by id , evtype
   ;
Quit ;

Kind regards,
==================
Paul M. Dorfman
Jacksonville, FL
==================

> From: Shannon [mailto:swheatma@FJC.GOV]
>
> Greetings- I am wondering if anyone has any thoughts about how to
> merge multiple observations into a single observation without
> overinflating the true number of matches.   Below is an example of a
> dataset in its current form (DATASET A) and the desired result
> (DATASET B).
>
> DATASET A:
>
> ID      PARTTRIG         PARTRESP       EVTYPE     ACTCODE
> FINALACT
>
> 304     T1                              DLSM            64
>           .
> 304     JD                              FPRO            64
>           .
> 304                     T1              DLSM            68
>           1
> 304                     JD              FPRO            68
>           2
> 1832    D1                              FPRO            64
>           .
> 1832    D1                              FPRO            64
>           .
> 1832                    D1              FPRO            68
>           .
> 1832                    D1              FPRO            68
>           2
> 1832                    P2              SMJD            68
>           1
>
>
>
> The goal is to match the records with ACTCODE=68 to the corresponding
> records with ACTCODE=64 by unique ID group, EVTYPE and where
> PARTTRIG=PARTRESP. The thing to avoid is multiple matches that result
> when records with similar matching criteria are merged using a MERGE
> statement. I think the key is to use SQL and perhaps set up a flag
> that prevents a record that is matched once from being matched a
> second time with each iteration until all possible match combinations
> are attained. An example of the desired result would be:
>
> DATASET B:
>
> ID      PARTTRIG         PARTRESP       EVTYPE    ACTCODE
>      FINALACT
>
> 304         T1          T1              DLSM            68
>           1
> 304         JD          JD              FPRO            68
>           2
> 1832    D1              D1              FPRO            68
>           .
> 1832    D1              D1              FPRO            68
>           2
> 1832                    P2              SMJD            68
>           1
>


Blue Cross Blue Shield of Florida, Inc., and its subsidiary and
affiliate companies are not responsible for errors or omissions in this
e-mail message. Any personal comments made in this e-mail do not reflect the
views of Blue Cross Blue Shield of Florida, Inc.