Date: Thu, 30 May 2002 04:07:52 +0000
Reply-To: sashole@bellsouth.net
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Paul Dorfman <paul_dorfman@HOTMAIL.COM>
Subject: Re: merge multiple observations into a single observation w/o ove
rinf lating matches
Content-Type: text/plain; format=flowed
Sig,
I think the first to share a bottle of Sigshine in your cave was Ian. In the
case at hand, however, I reckon the main reason was a solution having been
specifically solicited in SQL, and that kinda messed up our minds, and we
could not see the Data step right ... to say nothing of the lingering
hashover ...
Kind regards,
Hash-Man
----Original Message Follows----
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: merge multiple observations into a single observation w/o ove
rinf lating matches
Date: Wed, 29 May 2002 17:49:16 -0400
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.
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
